Find nth highest salary

Suppose you want to get 5^th highest salary from table. Simple solution looks like below.

a.   Sort the records in descending order of salary and limit the results to 5.

b.   In step a, you got 5 records, get the minimum out of them.

SELECT MIN(salary) FROM (SELECT salary FROM employee ORDER BY salary DESC LIMIT 10) as result;

mysql> SELECT MIN(salary) FROM (SELECT salary FROM employee ORDER BY salary DESC LIMIT 10) as result;
+-------------+
| MIN(salary) |
+-------------+
|    76543.67 |
+-------------+
1 row in set (0.00 sec)

Suppose you want to get all the details of 5^th highest salary employee, update query like below.

SELECT * FROM (SELECT * FROM employee ORDER BY salary DESC LIMIT 10) as result ORDER BY salary LIMIT 1;

mysql> SELECT * FROM (SELECT * FROM employee ORDER BY salary DESC LIMIT 10) as result ORDER BY salary LIMIT 1;
+----+-----------+----------+----------+
| id | firstName | lastName | salary   |
+----+-----------+----------+----------+
|  6 | Jyotsna   | PS       | 76543.67 | 
+----+-----------+----------+----------+
1 row in set (0.00 sec)

 

Previous                                                 Next                                                 Home