Window functions Updated November 2026

RANK and DENSE_RANK

RANK() OVER (ORDER BY salary DESC) gives tied rows the same rank and skips the following numbers (1, 2, 2, 4); DENSE_RANK() gives ties the same rank with no gaps (1, 2, 2, 3). Both restart per group with PARTITION BY, which makes them the tool for top N per group and nth highest value.

Syntax

RANK() OVER ([PARTITION BY cols] ORDER BY cols) | DENSE_RANK() OVER (...)

Examples

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

Result: name | salary | rnk | dense Ana | 9000 | 1 | 1 Ben | 8500 | 2 | 2 Carl | 8500 | 2 | 2 Dana | 7000 | 4 | 3

WITH r AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS d FROM employees
)
SELECT name, salary FROM r WHERE d = 3;

Result: Everyone on the third highest distinct salary

DENSE_RANK answers nth highest correctly even with ties.

WITH r AS (
  SELECT p.category, p.name, SUM(oi.qty) AS units,
         DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(oi.qty) DESC) AS d
  FROM order_items oi JOIN products p ON p.id = oi.product_id
  GROUP BY p.category, p.name
)
SELECT category, name, units FROM r WHERE d <= 2;

Result: Top two products per category by units sold

The window runs over the grouped rows, so aggregates can be used in its ORDER BY.

Common mistakes

Using RANK for the nth highest value skips numbers after ties, so WHERE rnk = 3 can return nothing; use DENSE_RANK.
Filtering on the rank in the same SELECT's WHERE is not allowed; wrap in a CTE or subquery.
Forgetting DESC when ranking from highest to lowest; the default ORDER BY ranks the smallest value 1.

Practice RANK and DENSE_RANK

These drills check your answer on live data, free, no account needed.

Rank within departmentReturn each employee's name, dept_id and their salary rank inside their own department, where 1 is the highest salary.
Dense rankReturn every employee's name, salary and DENSE_RANK of the salary across the whole company, where 1 is the highest.
Top two per categoryFor each category, return the category, product name and revenue (quantity times price) of its two best-selling products by revenue.

Questions

How do I find the nth highest salary in SQL?

DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE, then WHERE rank = n. It handles ties and works in SQLite, PostgreSQL, MySQL 8 and SQL Server.

Does RANK work without PARTITION BY?

Yes. Without PARTITION BY the whole result is one window and you get a global ranking.

ROW_NUMBERROW_NUMBER() OVER (ORDER BY ...) assigns 1, 2, 3 to rows; with PARTITION BY it restarts per group. Latest row per customer, remove duplicates, paginate.
PARTITION BYPARTITION BY inside OVER splits rows into groups so a window function restarts per group. Rank per department, share of group total, PARTITION BY vs GROUP BY.
Window functionsWindow functions compute rankings, running totals, shares and previous-row values without collapsing rows. The OVER clause, PARTITION BY, ORDER BY and frames.
LIMIT and OFFSETLIMIT returns only the first N rows of a query and OFFSET skips rows before that. Get the top 10, page through results and find the second highest value.