second highest salary - SQL

How to find out second maximum salary from all employee in employee table?

select max(salary) from emptable where salary < (select max(salary) from emptable);

OR

SELECT sal 
from emp 
ORDER BY sal DESC 
LIMIT 1, 1;

You will get only the second max salary.
And if you need any 3rd or 4th or Nth value you can increase the first value followed by LIMIT (n-1) ie. for 4th salary : LIMIT 3, 1;


OR

SELECT TOP 1 compensation FROM (
  SELECT TOP 2 compensation FROM employees
  ORDER BY compensation DESC
) AS em ORDER BY compensation ASC

Essentially:

• Find the top 2 salaries in descending order.
• Of those 2, find the top salary in ascending order.
• The selected value is the second-highest salary.