Finding Nth highest salary in a table is the most common question asked in interviews. Here is a way to do this task using dense_rank() function.
Consider the following table:
Employee
ename | sal |
---|---|
A | 23000 |
B | 31000 |
C | 24500 |
D | 35000 |
E | 28500 |
F | 31500 |
G | 39800 |
H | 51000 |
I | 39800 |
Query :
select * from( select ename, sal, dense_rank() over(order by sal desc)r from Employee) where r=&n; To find to the 2nd highest sal set n = 2 To find 3rd highest sal set n = 3 and so on.
Output :
DENSE_RANK :
- DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.
- This function accepts arguments as any numeric data type and returns NUMBER.
- As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
- In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.
Alternate Solution :
—————————————————————————————————————————————————————————————————————–
CREATE TABLE `Employee` ( `ENAME` varchar(225) COLLATE utf8_unicode_ci NOT NULL, `SAL` bigint(20) unsigned NOT NULL, PRIMARY KEY (`ENAME`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
————————————————————————————————————————————————————-
6th highest mysql> select * from ((select * from Employee ORDER BY `sal` DESC limit 6 ) AS T) ORDER BY T.`sal` ASC limit 1; Alternate use of Limit: select * from Employee ORDER BY `sal` DESC limit 5,1; // will return 6th highest +-------+-----+ | ENAME | SAL | +-------+-----+ | B | 300 | +-------+-----+ 1 row in set (0.00 sec)
——————————————————————————————————————————————————–
mysql> select * from Employee; +-------+-----+ | ENAME | SAL | +-------+-----+ | A | 100 | | B | 300 | | C | 200 | | D | 500 | | F | 400 | | G | 600 | | H | 700 | | I | 800 | +-------+-----+ 8 rows in set (0.00 sec)
Thanks to Vijay for suggesting this alternate solution.
Alternate Solution –
Suppose the task is to find the employee with the Nth highest salary from the above table. We can do this as follows:
- Find the employees with top N distinct salaries.
- Find the lowest salary among the salaries fetched by the above query, this will give us the Nth highest salary.
- Find the details of the employee whose salary is the lowest salary fetched by the above query.
Query:
SELECT * FROM Employee WHERE sal = ( SELECT MIN(sal) FROM Employee WHERE sal IN ( SELECT DISTINCT TOP N sal FROM Employee ORDER BY sal DESC ) )
The above query will fetch the details of the employee with the Nth highest salary. Let us see how:
- Consider N = 4.
- Starting with the most inner query, the query: “SELECT DISTINCT TOP 4 sal FROM Employee ORDER BY sal DESC” will produce the below result:
51000 39800 35000 31500
- The next outer query is: “SELECT MIN(sal) FROM Employee WHERE sal IN ( Result_Set_of_Previous_Query )“. This will return the below result:
31500
- You can see that the above returned result is the required 4th highest salary.
- Next is the most outer query, which is: “SELECT * FROM Employee WHERE sal = Result_of_Previous_Query“. This query will return the details of employees with 4th highest salary.
________________________ ename sal ________________________ F | 31500 | ________________________
Another Solution –
Here N = nth Highest Salary eg. 3rd Highest salary : N=3 .
SELECT ename,sal from Employee e1 where N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e2.sal > e1.sal)
This article is contributed by Rishav Shandilya. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.