SQL
Find Nth Highest Value Per Group
Discover how to retrieve the Nth highest (or lowest) value within distinct groups using SQL window functions, useful for ranking items per category.
WITH RankedSalaries AS (
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
FROM
employees
)
SELECT
employee_id,
department_id,
salary
FROM
RankedSalaries
WHERE
rn = 2; -- To find the 2nd highest salary per department
How it works: This snippet uses a Common Table Expression (CTE) with the `ROW_NUMBER()` window function to find the Nth highest value within each group. `PARTITION BY department_id` ensures ranking is done independently for each department. The `ORDER BY salary DESC` ranks salaries from highest to lowest. Finally, filtering by `rn = 2` retrieves the second-highest salary for each department, a common requirement in analytical reports.