How do you find the Second highest Salary?
This is the most common question asked in Interviews.
EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?
We can write a sub-query to achieve the result
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.