Interview Question of the Week #050 – Query to Retrieve Second Highest Salary of Employee

There are some questions which never gets old and I keep on seeing them again and again every time I participate in the interview to help hire qualified Developers and DBA. Here is another popular question which I often see.

Question: Write a query to retrieve Second (or Nth) highest salary of an employee from a table.

Answer: Here is the generic query for the same. You can use the following query for any other similar logic.

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

In the above example, the inner query uses a value of the outer query in its filter condition, meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
MySQL – Date and Time Related System Functions
Next Post
SQL SERVER – The Best Time to Buy – Devart Christmas Price Fall

Related Posts

No results found

10 Comments. Leave new

  • Do You think that ranking (dense_rank) could be a better way for similar queries in terms of clarity and execution cost ? Like:

    WITH SalaryRanking_CTE AS
    (
    SELECT *,
    DENSE_RANK() OVER (ORDER BY Salary DESC) as Ranking
    FROM Salary
    )
    SELECT *
    FROM SalaryRanking_CTE
    WHERE Ranking = n

    Reply
  • Hi Pinal, I’ve tried the script on SQL2012 and it does not recognized N in the Where clause. The error reads: Msg 207, Level 16, State 1, Line 4
    Invalid column name ‘N’.
    Do I miss something here? Thank you!

    Reply
    • N is a variable.. declare @N int =2 .. then replace N with @N and this give you the second highest

      Reply
    • Yvonne, if you’d like to run his query, replace “N” with either a constant (e.g. “2” to get the second highest salary) or declare a variable “@N” and set it to “2” (or whatever number you’d like).

      Reply
  • Richard Armstrong-Finnerty
    December 27, 2015 6:26 pm

    — Using ROW_NUMBER()
    SELECT *
    FROM
    (
    SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) as rownum ,*
    FROM Employee
    ) AS Employee
    WHERE rownum = 2

    Reply
  • abdulhannanijaz
    December 28, 2015 7:06 pm

    WE can also use Offset and Fetch to get 2nd highest value
    –Sample For Adventure works 2012

    SELECT *
    FROM HumanResources.EmployeePayHistory
    ORDER BY Rate DESC
    OFFSET 1 ROW FETCH NEXT 1 ROW ONLY

    Reply
  • abdulhannanijaz
    December 28, 2015 7:08 pm

    Offset can be used now to get value of any level
    –For adventure works 2012
    SELECT *
    FROM HumanResources.EmployeePayHistory
    ORDER BY Rate DESC
    OFFSET 1 ROW FETCH NEXT 1 ROW ONLY

    Reply
  • Simple query: select top 1 * from(select top 2 * from employee order by salary desc) order by salary asc

    Reply

Leave a Reply

Menu