SQL · Pattern 11 — Interview SQL Patterns
Nth Highest Salary
Uses DENSE_RANK() to retrieve the nth highest salary.
Source data — Pattern 11: Interview SQL Patterns
All queries run against the shared employees table (see schema.sql). Topics 71 and 72 introduce an orders table to solve consecutive records and gaps-and-islands problems. Topics 73 and 74 introduce a monthly_sales table to demonstrate PIVOT and UNPIVOT operations.
What it does
Uses DENSE_RANK() to retrieve the nth highest salary.
Problem
Find the nth highest salary.
SELECT *
FROM (
SELECT first_name,salary,DENSE_RANK() OVER(ORDER BY salary DESC) rnk
FROM employees
) t
WHERE rnk = :N;
Result
