Sql query that numerates the returned result – Education Career Blog

How to write one SQL query that selects a column from a table but returns two columns where the additional one contains an index of the row (a new one, starting with 1 to n). It must be without using functions that do that (like row_number()).
Any ideas?

Edit: it must be a one-select query

,

You can do this on any database:

SELECT  (SELECT COUNT (1) FROM field_company fc2
         WHERE fc2.field_company_id <= fc.field_company_id) AS row_num,
        fc.field_company_name
FROM    field_company fc

,

SET NOCOUNT ON

DECLARE @item_table TABLE
(
 row_num INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, --THE IDENTITY STATEMENT IS IMPORTANT!
 field_company_name VARCHAR(255)
)

INSERT INTO @item_table 
    SELECT field_company_name FROM field_company

SELECT  * FROM @item_table

,

if you are using Oracle or a database that supports Sequence objects, make a new db sequence object for this purpose. Next create a view, and run this.

insert into the view as select column_name, sequence.next from table

,

In mysql you can :

SELECT Row,Column1
FROM (SELECT @row := @row + 1 AS Row, Column1 FROM table1 ) 
As derived1

,

I figured out a hackish way to do this that I’m a bit ashamed of. On Postgres 8.1:

SELECT generate_series, (SELECT username FROM users LIMIT 1 OFFSET generate_series) FROM generate_series(0,(SELECT count(*) – 1 FROM users));

I believe this technique will work even if your source table does not have unique ids or identifiers.

,

On SQL Server 2005 and higher, you can use OVER to accomplish this:

SELECT rank() over (order by company_id) as rownum
, company_name
FROM company

Leave a Comment