Adding a auto generated id to record in the results

Question

I need to add an auto generated auto-increment id to a query results. For example, for the query

SELECT TOP 3 Users.Reputation FROM Users ORDER BY 1 DESC

Instead of getting

• 101
• 100
• 99

I want to get

• 1, 101
• 2, 100
• 3, 99

How can I do it?

Answer 1

The easiest way if it is an SQL Server, then this one:

SELECT TOP 3 
ROW_NUMBER() OVER(ORDER BY Users.Reputation DESC), 
Users.Reputation 
FROM Users

Update:

I checked the Query Execution plan, and the inner order by, which defines the logical order of the rows within each partition of the result set (I did not use a specified partitioning condition in this case) using a Sort step, which is the same as by a normal sort:

SELECT Users.Reputation
FROM Users
ORDER BY Users.Reputation DESC

After this step there are two intresting step: Segment and Sequence Project. The second one is telling that it is compution over an ordered dataset. The last steps are a TOP and a SELECT.

I'm intrested in the drawbacks, but it seems OK.

And just a note at the end: with a normal OrderBy with TOP:

 SELECT TOP 3 Users.Reputation
    FROM Users
    ORDER BY Users.Reputation DESC

The sorting is easier, but almost a same : Sort (Top N sort)

Answer 2

How about:

;WITH CTE AS 
(SELECT
    RowNum = ROW_NUMBER() OVER(ORDER BY Reputation DESC),
    Users.Reputation
 FROM
    Users
)
SELECT TOP 3
    RowNum, Reputation
FROM
    CTE
ORDER BY  
    RowNum

This is a CTE (Common Table Expression) available in SQL Server 2005 and newer, combined with the ROW_NUMBER() ranking function, also available in 2005 and newer.

It creates an "on-the-fly" view for just the next statement and allows to do some extra processing first, before selecting. The ROW_NUMBER() just adds a consecutive number to each row in the order defined in the OVER (ORDER BY ....) clause. So the first row gets #1, the second #2 and so on.

Read more about CTE's here: MSDN - Using Common Table Expressions

Read more about ROW_NUMBER and other ranking functions here: MSDN ROW_NUMBER