Find 2nd highest salary best possible ways in mysql and PHP

317

This question mostly ask at the time of interview from the candidates who are freshers or 1 year experience. Even this question asked from me may times when i was comer in web development field.

So i am going to show you some possible ways to find 2nd or nth highest salary.

 

For Exp: Employee Table(ET)

EID EmployeeName Salary
1 Employee-1 20,000
2 Employee-2 22,000
3 Employee-3 21,000
4 Employee-4 19,000
5 Employee-5 21,000

1:- How to find nth highest salary no condition
In this case you can use very basic limit and max methods of sql

SELECT DISTINCT(Salary) FROM ET ORDER BY Salary DESC LIMIT n-1,1

Where n is the position you are looking for
If n= 2 query will become

SELECT DISTINCT(state_id) FROM crm_cities ORDER BY state_id DESC LIMIT 1,1

Output will be: 21,000

2:- How to find all nth highest salary where the condition is you have to find all 2nd highest paid employees.
In this case you can use sub query feature of sql.

SELECT Salary FROM ET WHERE Salary = (SELECT DISTINCT(Salary) FROM ET ORDER BY Salary DESC LIMIT n-1,1)

Where n is the position you are looking for
If n= 2 query will become

SELECT Salary FROM ET WHERE Salary = (SELECT DISTINCT(Salary) FROM ET ORDER BY Salary DESC LIMIT 1,1)

Output will be: 21,000 , 21,000

3:- How to find 2nd highest salary without using LIMIT conditions.
In this case you can use NOT IN conditions in sql

SELECT MAX(Salary) FROM ET
WHERE Salary NOT IN (SELECT MAX(Salary) FROM ET)

Output will be: 21,000

4:- How to find 2nd highest salary without using LIMIT and Sub-Query
In this case you can use self-join method

SELECT MAX( E1.Salary ) FROM ET E1, ET E2 WHERE E1.Salary < E2.Salary
Output will be: 21,000

Hope this will help you to find the right answer as your need.. :)