autonumber in select statement in SQL Server

Question

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

select * from tbl1

will give me everything from table.

The result I'd like to get is..

1         data
2         data
3         data

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

like..

select (for autonumber), * from tbl1

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

Answer 1

Use ROW_NUMBER:

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

To filter the results based on the row number use this:

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