获取select结果集的行号 sql server row_number <转>

This article will explain how we can add sequence row number to a SQL select query starting from 1 onwards. This can be achieved by using built in SQL function ?ROW_NUMBER()?. This function simply generates row number for each row in the result. You can specify the partition and order by criteria. This is how it works:

e.g. Suppose we have a table ?Employees?.

SELECT EmployeeId, EmployeeName, Salary 
    FROM Employees

EmployeeId   EmployeeName     Salary
-------------------------------------
1002         Alden            4000
2343         Lawson           4500
2004         Barbra           4800
1105         Marsden          4500
3116         Mac              5000

1.Use of ROW_NUMBER() will assign sequence number to rows as:

SELECT ROW_NUMBER() 
        OVER (ORDER BY EmployeeName) AS Row, 
    EmployeeId, EmployeeName, Salary 
FROM Employees

Row   EmployeeId   EmployeeName     Salary
-------------------------------------------
1     1002         Alden            4000
2     2343         Lawson           4500
3     2004         Barbra           4800
4     1105         Marsden          4500
5     3116         Mac              5000

 

2.Using ROW_NUMBER() for calculating Nth highest salary

We can utilize this function for calculating Nth highest salary of a employee. Suppose we want to find employee with 4th highest salary. This can be done as:

SELECT * FROM
    (SELECT ROW_NUMBER() 
        OVER (ORDER BY Salary) AS Row, 
        EmployeeId, EmployeeName, Salary 
    FROM Employees) AS EMP
WHERE Row = 4

Row   EmployeeId   EmployeeName     Salary
-------------------------------------------
4     1105         Marsden          4500

 

3.Using ROW_NUMBER() in case of pagination

This can also be used for getting rows which belongs to a particular page only. This is very common scenario of a business application where we have lots of rows in database and we want to filter based on page number.

SELECT * FROM
    (SELECT ROW_NUMBER() 
        OVER (ORDER BY EmployeeName) AS Row, 
        EmployeeId, EmployeeName, Salary 
    FROM Employees) AS EMP
WHERE Row BETWEEN 2 AND 4

Row   EmployeeId   EmployeeName     Salary
-------------------------------------------
2     2343         Lawson           4500
3     2004         Barbra           4800
4     1105         Marsden          4500