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) 

print

7 thoughts on “SQL SERVER – Find Nth /Second/Third Highest Salary – Common interview Question

  1. Author’s gravatar

    Hi Varun,

    We can also fetch 2nd Highest Salary from following query:

    select max(salary) from Employeedetails
    where salary not in (select top 1 salary from Employeedetails order by salary desc )

    Thanks & Regards,
    Fazal Vahora

  2. Author’s gravatar

    Hi Fazal,

    Sure.. we can also get the result using your query.

  3. Author’s gravatar

    Please tell me the possible watys to get not just the nth max salary but also the details of the employee who is getting the n th highest salary. Thanks.

  4. Author’s gravatar

    Great post!! by the way heere are couple of more ways to find second highest salary in SQL

  5. Author’s gravatar

    HI DIS IS SATISH..

    FIND 2 ND HIGHEST SAL…
    ANS:SELECT MAX(SAL) FROM EMP
    WHERE
    SAL<(SELECT MAX(SAL) FROM EMP.

  6. Author’s gravatar

    SELECT TOP 1 Salery FROM ( SELECT TOP 2
    Salery FROM employee ORDER BY Salery DESC)
    AS emp ORDER BY Salery ASC

  7. Author’s gravatar

    I got the solution:
    Multiple Sql server queries to get second,third,fourth or nth highest/maximum salary from employee table
    http://www.webcodeexpert.com/2014/08/sql-server-query-to-get.html

Leave a Reply to Periyar Cancel reply

Your email address will not be published. Required fields are marked *


five × 8 =