Third Highest Salary in SQL Server

For the majority of data retrievals, there are workarounds. In SQL, sub-queries can be used, although joins are also an option in the majority of situations. Most of the time, you can achieve a goal by using built-in functions rather than inner queries.

The example that follows shows how to extract the third-highest salary from the EMP table in different ways.

Method-1:

SELECT * FROM Emp WHERE Sal = (
SELECT MAX(Sal) FROM Emp WHERE Sal <
(
SELECT MAX(Sal) FROM Emp WHERE Sal < (SELECT MAX(Sal) FROM Emp)))

Method-2:

SELECT * FROM
(SELECT *, dense_rank() over (ORDER BY Sal DESC) RNK FROM Emp) E
where rnk = 3

Method-3

SELECT * FROM
(SELECT *, rank() over (ORDER BY Sal DESC) RNK FROM Emp) E
where rnk = 3

Method-4:

SELECT * FROM
(SELECT *, row_number() over (ORDER BY Sal DESC) RNK FROM Emp) E
WHERE rnk = 3

However, methods 3 and 4 aren’t very useful if the same wage is paid to numerous people. I would advise using dense_rank (Method-2).