Wednesday, November 3, 2010

Get Nth Highest Salary in SQL Server

Using Inline View:
SELECT * FROM (
SELECT EID,ESalary,Row_number() OVER (ORDER BY ESalary) AS Row
FROM YourEmployeeTableName
) t1
WHERE Row=2

Using Dense_Rank:
SELECT * FROM
(SELECT DISTINCT Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
FROM YourEmployeeTableName
WHERE Salary IS NOT NULL) AS s
WHERE rnk = 3 ;

Using SubQueries :
SELECT TOP 1 salary FROM emp WHERE salary
NOT IN(SELECT TOP 3 salary FROM emp ORDER BY salary DESC) ORDER BY DESC
(Here Change Top 3 based on the Nth value)

No comments:

Post a Comment