Finding the Nth Highest Salary from table

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

4 Responses to Finding the Nth Highest Salary from table

  1. anjana says:

    nice article.but try to check it once before submitting article,because some people blindly copies the solution for the queries.if there is any mistake in the solution,they also do the same mistake without thinking .
    i am doing the small correction in your query:

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

  2. kiran says:

    can u check this for duplicate salaries in a table. i,e in a table we can have n no.of employees with same salary.

  3. Thanks anjana for your corrections. 🙂

  4. SELECT `salary`
    FROM emp X
    WHERE 2 = (
    SELECT Count( DISTINCT `salary` )
    FROM emp
    WHERE `salary` >= X.`salary` )

    this query will work for more then one person if highest salary.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: