Saturday, November 14, 2009

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

Wednesday, November 11, 2009

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