Top 10 MYSQL Interview Questions and Answers

Q# 1) Write a SQL query to find out 2nd highest Salary? How to find nth highest salary? write down the SQL query.

Solution:

emp

To find out 2nd highest salary the SQL query is:

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

By using limit we can write the query as below to get 2nd highest salary. But the problem is that if same salary for more than one employee than it will display only one.

 select  DISTINCT(salary) 
 from emp 
 order by salary desc limit 1,1

Query to find out n th highest salary

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

/* 	nth highest salary  */

SELECT * 
FROM emp Emp1
WHERE (N-1) = ( /* N = user value like 5 th higjhest salary */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM emp Emp2
WHERE Emp2.Salary > Emp1.Salary)