# How to find Nth highest salary from a table

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 TOPNsal 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*” will produce the below result:**4**sal FROM Employee ORDER BY sal DESC

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.

Attention reader! Don’t stop learning now. Learn SQL for interviews using **SQL Course**** **by GeeksforGeeks.