Search content within the blog

Wednesday, May 20, 2009

Find the nth highest or lowest salary in sqlserver

To find the highest or lowest salary from the salary table the query is as follows

To find the 3rd Highest salary
SELECT * FROM salary s1 WHERE 3 = (SELECT COUNT(DISTINCT (s2.salary))FROM salary s2
WHERE s2.salary >= s1.salary)

change 3 to whatever value you want or pass it as a parameter


To fin the 2nd Lowestt salary
SELECT * FROM salary s1 WHERE 2 = (SELECT COUNT(DISTINCT (s2.salary))FROM salary s2
WHERE s2.salary <= s1.salary)

change 3 to whatever value you want or pass it as a parameter

No comments:

Post a Comment