Autonumber in select statement in SQL Server

Question

I would like to create a select query request with autonumber .. like ..

select * from tbl1 

will give me everything from the table.

The result that I would like to get is ...

 1 data 2 data 3 data 

So how can I do to get this number .. ??

as..

 select (for autonumber), * from tbl1 

the data in my table will be repeated (no unique data)

Answer 1

Use ROW_NUMBER :

 SELECT ROW_NUMBER() OVER (ORDER BY col1) AS rn, * FROM tbl1 

To filter results based on line number, use this:

 SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY col1) AS rn, * FROM tbl1 ) T1 WHERE rn = 5 

Answer 2

You may need to find an identity bias, for example. last identifier of the second table:

 DECLARE @lastAutoID int SET @lastAutoID = abs(( Select max(convert(float,[ConsID])) FROM [RXPIPEDB]...[consumption] ) ) 

Then use ROW_NUMBER ():

 @lastAutoID + ROW_NUMBER() OVER (ORDER BY oldICN_str)