Built in SQL Function ROW_NUMBER

Today our discussion matter on built in SQL function ROW_NUMBER(). This function simply generates row number for each row in the result.

e.g. Suppose we have a table Employees.

SELECT EmployeeId, EmployeeName, Salary 
    FROM Employees

EmployeeId   EmployeeName   
-------------------------- 
P0001         	Emp1             
P0002          	Emp2             
P0003          	Emp3             
P0004          	Emp4           
P0005         	Emp5            

ROW_NUMBER() when assign sequence number to rows as:

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

Row   EmployeeId   EmployeeName 
--------------------------------
1     P0001         	Emp1             
2     P0002          	Emp2             
3     P0003          	Emp3             
4     P0004          	Emp4           
5     P0005         	Emp5    

ROW_NUMBER() when 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     P0004         Emp4             4500
				

ROW_NUMBER() when 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 
--------------------------------
2      P0002          	Emp2             
3      P0003          	Emp3             
4      P0004          	Emp4