Thursday, June 21, 2007

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


kick it on DotNetKicks.com


हेमंत गुप्ता


7 comments:

Abhishek Kumar Singh said...

thanx for info

HemantG said...

Thank you Abhishek

Anonymous said...

I think you should use dense_rank() instead of row_number(). The solution with row_number() will work only if each row in the table has the unique salary.

Anonymous said...

Thanks dada Still I am confused.Please suggest me the easier way.
Satya,bbsr,Orissa

HemantG said...

if you can provide me what exactly you need, i might be able to help you.

Anonymous said...

thank you so much boss :)

Eijaz Sheikh said...

thanks a lot Hemant :)