Search This Blog

Friday, August 10, 2007

Getting the nth highest salary along with the details

For 2nd Highest Salary we can use the queries below

Using TOP Clause
Select * From [Emp_Salary]
Where [EMP_Sal] In (Select Min([EMP_Sal])
From [ERecords]
Where [EMP_Salary] In (Select Distinct Top 2 [EMP_Sal]
From [ERecords]
Order By [ESal] Desc))

Second Way
Select * From [Emp_Salary] [ES1]
Where (2 = (Select Count(Distinct [Emp_Sal])
From [Emp_Salary] [E2]
where [E1].[ESal] <= [E2].[ESal]) )

In the above corresponingly change the number from 2 to 'n' to fetch the nth highest value

No comments: