SQL SERVER – Find Nth /Second/Third Highest Salary – Common interview Question

This is a common question in Interview “find the second highest salary of Employee details table”. I hope most of my friends also have faced this question. Here I am explaining about two different methods-

Using Row_number()
Without using any SQL Function.

This is my Employeedetails table.

Method 1: Using Row_number()

Here I am showing third highest salary.

Method 2 – With no SQL Server Function

Another method: Suggested by Fazal – one of the regular blog reader of SQLINFO.

SELECT MAX(salary)

FROM   employeedetails

WHERE  salary NOT IN (SELECT TOP 1 salary

                      FROM   employeedetails

                      ORDER  BY salary DESC)