Q# 1) Write a SQL query to find out 2nd highest Salary? How to find nth highest salary? write down the SQL query.
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)