Nth Salary

One of the common questions in interviews is how to find out 2nd best salary or find 5th best salary etc...

There are somany ways to write this kind of query.

Let me show you some of them....

--Find 3rd highest salary

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM tblSalary
ORDER BY salary DESC) S
ORDER BY salary

Here in the Sub query we are trying to get the TOP 3 salaries based on the order by. In the outer query order it once again but this time it is ASC order to reverse order so that we can use "TOP 1" to get the 3rd best salary.

If we go and generilize this query to work for any kind of result then


--Find Nth highest salary
SELECT TOP 1 salary FROM (
SELECT DISTINCT TOP N salary FROM tblSalary ORDER BY salary DESC) S
ORDER BY salary

Just replace the replace the Number with "N" in the sub query. Now if you want 5th best salary just replace N with 5. Simple...

Dont try to remember...understand the logic/concept....