How to get second highest or nth maximum salary of an Employee table is one of the most frequently asked Mysql/SQL interview question.
PHP Mysql Query
Suppose we have the following simple database table called Employee that has 3 columns named Employee ID,Employee Name and Salary:
Employee
##########################################
Employee_ID —– Employee_Name ——- Salary
1: —————– Avanish——— ——- 1000
2: —————– Abhishek——- ——- 1500
3:———— —– Tathagat——– ——- 700
4:———— —– Akarsh —————– 900
5: —————– Jagdish —————- 1700
##########################################
1:Now Query to get the highest salary:
|
1 |
SELECT MAX(Salary) FROM Employee |
2:Now Query to get the Second highest salary:
|
1 |
SELECT Employee_Name,Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1 |
Make the the query selection in descending order with salary and use limit.
Why LIMIT 1 , 1 ? => first 1 is for the starting point and second one is for the total record to be fetch. Like currently we are trying to get second highest salary so start point will 1( index starts from 0 ( zero ) ) ,and we need to get only second highest so need to select only one record.
3:Now find the nth highest salary :
|
1 |
SELECT Employee_Name,Salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1 |
