Suppose you want to get 5th 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.
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)
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)
This post first appeared on Java Tutorial : Blog To Learn Java Programming, please read the originial post: here
