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?.
EmpName | Salary |
---|---|
Steve | 150 |
Mark | 100 |
James | 120 |
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.
SELECT Sal FROM emp
ORDER BY
sal DESC
Create view v as select * from Employee order by salary DESC;
select * from v where rownum = n;
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