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)
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
Sure. That would also work.
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!
N is a variable.. declare @N int =2 .. then replace N with @N and this give you the second highest
Thanks for adding a comment.
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).
— Using ROW_NUMBER()
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) as rownum ,*
FROM Employee
) AS Employee
WHERE rownum = 2
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
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
Simple query: select top 1 * from(select top 2 * from employee order by salary desc) order by salary asc