CODEWITHSUNDEEP
sql-server
william
william

Reputation: 7664

autonumber in select statement in SQL Server

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)

Upvotes: 12

Views: 77444

Answers (2)

Chagbert
Chagbert

Reputation: 758

You may need to find the identity's offset e.g. last ID of 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)

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838056

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

Upvotes: 30

Related Questions