# 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 :

1. 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.
2. This function accepts arguments as any numeric data type and returns NUMBER.
3. 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.
4. 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.

This article is attributed to GeeksforGeeks.org

SQL SQL

code

load comments