Sql query that numerates the returned result.

Question

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

Answer 1

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

Answer 2

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

Answer 3

In mysql you can :

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

Answer 4

found this:
http://support.microsoft.com/kb/186133
nice way

Answer 5

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

Answer 6

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.

Answer 7

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