Ye Haath Salamat Hain Jab Tak
Is Khoon May Hararat Hai Jab Tak
Is Dil May Sadaqat Hai Jab Tak

Adding Row Number to SQL SELECT result

ntroduction 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

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

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

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



happy programming :D