In so many interviews Interviewer will ask you a very simple MySQL query. And the question is :
How to select 2nd,3rd or the Nth highest salary from a table in MySQL ?
If you will answer with sub query like below query:
|
1
2
3
4
5
|
SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee)
OR
SELECT MAX(Salary) From Employee WHERE Salary < ( SELECT Max(Salary) FROM Employee);
|
Next question might be asked like:
How to select 2nd,3rd or the Nth highest salary from a table in MySQL without using sub query ?
To get the second highest salary of an employee here is the MySQL query:
|
1
|
select * from table order by Salary desc limit 1,1
|
If you wanted to get 3rd highest salary then here is below MySQL query to get the 3rd highest salary:
|
1
|
select * from salary GROUP BY salary order by salary desc LIMIT 2,1
|
If you wanted to get nth highest salary then here is below MySQL query to get the nth highest salary:
|
1
|
select * from salary GROUP BY salary order by salary desc LIMIT n-1,1
|
for example if you wanted to write MySQL query to get 4th highest salary of the employee then the query will be like:
|
1
|
select * from salary GROUP BY salary order by salary desc LIMIT 3,1
|
...