How do you find the Nth highest salary in SQL?
How do you find the Nth highest salary in SQL?
Clarify the requirement first: the Nth highest distinct salary, or the Nth row (where ties count separately)? The usual interview meaning is "Nth distinct", and the cleanest answer uses DENSE_RANK.
SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t
WHERE rnk = 3;
Returns no row if fewer than three distinct salaries exist. Wrap it in a scalar subquery to return NULL instead:
SELECT (SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t
WHERE rnk = 3) AS third_highest;
-- PostgreSQL, MySQL
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.