Retrieving Nth Salary in SQL Server 2000 and 2005

Recently I came across a post in ASP.Net Forum where a user had asked for a Query for Nth Salary retrieval from employee table using SQL query. Strangely on further searching the net I found quite a few people had posted similar query on the net with no answered, here is my answer

SQL Server 2000

Declare @Table Table
(
EID INT Identity,
ESalary INT
)

Insert into @Table(ESalary)
Values (400)

Insert into @Table(ESalary)
Values (4100)

Insert into @Table(ESalary)
Values (1400)

Insert into @Table(ESalary)
Values (2400)

Insert into @Table(ESalary)
Values (4300)


Select * From @Table [T1] Where
(2 = (Select Count(Distinct [ESalary]) From @Table [T2] where [T1]।[ESalary] <= [T2].[ESalary])) Just replace 2 by any digit 3,4,5। and it will work

SQL Server 2005

SQL Server 2005 provides a new function, Row_Number(), for generating row numbers, this function is used in conjunction with over for generating Ranks/ Rowids staring from 1 for the first row in each partition. I will be posting in more detail regading this feature in my next post. meanwhile you should be able to see how simple the job has become with SQL Server 2005

SELECT

*

FROM (

SELECT EID,ESalary,row_number() OVER (ORDER BY ESalary) AS Rank

FROM @Table

) t1

WHERE Rank=2

हेमंत गुप्ता