Geek asks and answers

Adding an Automatically Generated Identifier to Record Results

I need to add an automatically generated auto increment identifier to the query results. For example, for a request

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

Instead of getting

  • 101
  • 100
  • 99

I want to receive

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

How can i do this?

+5
source share
2 answers

What 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) expression, available in SQL Server 2005 and newer, combined with a ranking function ROW_NUMBER(), also available in 2005 and later.

" " . ROW_NUMBER() , OVER (ORDER BY ....). , # 1, # 2 ..

CTE : MSDN -

ROW_NUMBER : MSDN ROW_NUMBER

+5

, SQL Server, :

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

Update:

, ( ), Sort, , :

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

: . , . TOP SELECT.

, .

: OrderBy TOP:

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

, : Sort (Top N sort)

+6

More articles:


All Articles