Window functions Updated November 2026

ROW_NUMBER

ROW_NUMBER() OVER (ORDER BY col) gives each row a unique sequential number starting at 1, with no ties. Add PARTITION BY to restart at 1 for each group, then filter on the number in an outer query to keep the first (or nth) row per group: WHERE rn = 1 returns each customer's first order.

Syntax

ROW_NUMBER() OVER ([PARTITION BY cols] ORDER BY cols)

Examples

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, name, salary FROM employees;

Result: rn | name | salary 1 | Ana | 9000 2 | Ben | 8500 3 | Carl | 8500

Ben and Carl tie but still get different numbers; use RANK if ties should share a number.

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM orders
)
SELECT customer_id, id, order_date FROM numbered WHERE rn = 1;

Result: Each customer's first order

ORDER BY order_date DESC would give the latest order instead.

WITH d AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM customers
)
DELETE FROM customers WHERE id IN (SELECT id FROM d WHERE rn > 1);

Result: Removes duplicate customers, keeping the lowest id per email

Common mistakes

Filtering WHERE ROW_NUMBER() OVER (...) = 1 directly fails; window functions are evaluated after WHERE, so use a CTE or subquery.
Leaving out ORDER BY in the OVER clause numbers rows in an arbitrary order that can change between runs.
Using ROW_NUMBER for a top-N with ties when you wanted all tied rows; use RANK or DENSE_RANK instead.

Practice ROW_NUMBER

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

Each customer's first orderFor each customer, return the customer_id and the id of their first order (earliest date, then lowest id). Count every order, whatever the status.
Top earner per departmentReturn the dept_id, name and salary of the highest-paid employee in each department. Ignore employees without a department.

Questions

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER always gives 1, 2, 3 with no ties. RANK gives tied rows the same number and skips the next (1, 2, 2, 4). DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3).

How do I get the latest record per group?

ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY date_col DESC) AS rn in a CTE, then WHERE rn = 1. PostgreSQL also offers SELECT DISTINCT ON (group_col) ... ORDER BY group_col, date_col DESC.

RANK and DENSE_RANKRANK() and DENSE_RANK() rank rows and give ties the same rank; RANK skips numbers after a tie, DENSE_RANK does not. Nth highest salary and top N per group.
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.
WITH (CTE)A common table expression defines a named temporary result with WITH name AS (...) that the main query uses like a table. Examples, several CTEs, recursion.