Finding the Nth Highest Salary from table

June 2, 2007

This is the most common question asked in every interview for the database. As I am more familiar with mysql , Its so simple with the following query

SELECT * FROM employee ORDER BY salary DESC LIMIT 2,1

This query will bring the second highest salary from the table employee.

But Query should work with every database so here is the correct query and its details explaination which will bring specified highest salary from the table

select * from employee emp where 3 = (select count(*) from employee emp where a.salary <= b.salary) ;

I made a details comparision set about the operations of query.

Table employee is having the 6 record with salary ranging from 1000 to 6000

Query:
====
SELECT * FROM employee emp WHERE 3 = (SELECT COUNT(*) FROM employee emp WHERE a.salary <= b.salary) ;

Detail Record Condition Check
=====================
1)a <= b
1000<=1000
1000<=2000
1000<=3000
1000<=4000
1000<=5000
1000<=6000
[count=6]

2)a <= b
2000<=1000
2000<=2000
2000<=3000
2000<=4000
2000<=5000
2000<=6000
[count=5]

3)a <= b
3000<=1000
3000<=2000
3000<=3000
3000<=4000
3000<=5000
3000<=6000
[count=4]

4)a <= b
4000<=1000
4000<=2000
4000<=3000
4000<=4000
4000<=5000
4000<=6000
[count=3]

5)a <= b
5000<=1000
5000<=2000
5000<=3000
5000<=4000
5000<=5000
5000<=6000
[count=2]

6)a <= b
6000<=1000
6000<=2000
6000<=3000
6000<=4000
6000<=5000
6000<=6000
[count=1]

Result : 4000 as its count is matching the condition ie. 3

Advertisements