MiddleCodeOccasionalNot answered yet
Write a query for the N-th highest salary with ties
Return the N-th highest salary, treating equal salaries as the same rank.
-- Employee(id, name, salary, dept_id, manager_id)
-- parameter :N is the desired rank
-- write your query hereWrite the query.
Rank salaries with a window function and filter on the rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM Employee) t WHERE rnk = :N. DENSE_RANK gives tied salaries the same rank with no gaps in the numbering.
- ✗Using
ROW_NUMBERwhere ties must share a rank (useDENSE_RANK) - ✗Relying on
OFFSETwithoutDISTINCT, so ties shift the position - ✗Confusing
RANK(gaps) withDENSE_RANK(no gaps)
- →When would you choose
RANKoverDENSE_RANKfor this problem? - →How does
ROW_NUMBERbehave differently fromDENSE_RANKon tied salaries?
Contents
Task
Return the N-th highest salary from Employee, treating equal salaries as the same rank.
Solution
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employee
) t
WHERE rnk = :N;Key points
DENSE_RANKgives tied salaries one rank and leaves no gaps in the numbering.ROW_NUMBERwould give every row a unique number — wrong when ties must share a rank.RANKalso shares a rank on ties but then skips the following numbers.
Contents
