Find the Nth Highest Salary in a table.

In previous database posts we have seen how to retrieve 2nd highest salary (in whole table and for each department). But, what if we want to find the Nth highest salary, where N can be any number whether it’s the 3rd highest, 4th highest, 5th highest, 10th highest, etc?.

EmpNameSalary
Steve150
Mark100
James120

Try to solve on your own before looking into solution.

The first solution that has come to your mind. (Nothing came?? Read our previous posts)


SELECT Salary FROM employee ORDER BY salary DESC LIMIT N-1,1

But As we mentioned in previous post too that this solution won’t return correct value if there are two records for Nth Highest Salary.

Correct Sql :

SELECT *
FROM Employee E1
WHERE (N-1) = (
        SELECT COUNT(DISTINCT(E2.Salary))
        FROM Employee E2
        WHERE E2.Salary > E1.Salary)

Explanation : Suppose we want to find second highest salary in above table.We process each row one by one and check for N-1 distinct(Here 2-1 = 1 in this example ) salaries greater than current salary.We start with first row with Salary equal to 150 since 100 and 120 both are less than 150 so there is no rows with salary greater than 150 so sub query will return 0 which is not equal to 1.Next We process second row with salary equal to 100.Both 150 and 120 are greater than 100 so there are two rows with salary greater than 100 so right sub query will return 2 which is also not equal to 1. Now we come to third row with salary equal to 120. As there are only one row with salary greater than 120 so sub query will return 1 which is also equal to N-1 so we print this row.

3 Thoughts on “Find the Nth Highest Salary in a table.

  1. Ankur Wani on October 26, 2013 at 2:54 pm said:

    SELECT Sal FROM emp
    ORDER BY
    sal DESC

  2. Vishal Gangwar on August 25, 2014 at 12:25 pm said:

    Create view v as select * from Employee order by salary DESC;
    select * from v where rownum = n;

  3. Ambalika Smiti on August 31, 2016 at 7:17 pm said:

    we can use Dense_Rank() function to solve this

    SELECT EmpName, Salary, DENSE_RANK() OVER (ORDER BY E1.Salary) AS “Salary Rank”
    FROM Employee E1
    WHERE Salary Rank=N

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation