SQL
Find Nth Highest Value Using ROW_NUMBER
Retrieve the Nth highest value from a dataset, a common task for ranking and analytics, utilizing powerful window functions like ROW_NUMBER within a CTE.
WITH RankedSalaries AS (
SELECT
employee_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
FROM
employees
)
SELECT
salary
FROM
RankedSalaries
WHERE
rn = 3; -- For 3rd highest salary
How it works: This query finds the Nth highest value (in this case, the 3rd highest salary) using a Common Table Expression (CTE) and the `ROW_NUMBER()` window function. `ROW_NUMBER()` assigns a unique sequential integer to each row within its partition (here, the entire table) based on the specified order. By ordering salaries in descending order, the highest salary gets `rn=1`, the second highest `rn=2`, and so on. The CTE allows for a clean separation of ranking and selection.