DocsWindow FunctionsROW_NUMBER

ROW_NUMBER

Window Functions

Concept

ROW_NUMBER() assigns a unique sequential integer to each row within its partition, starting at 1. The assignment is based on the ORDER BY specified in the OVER clause. Unlike RANK, ROW_NUMBER never produces ties — even if two rows have identical ORDER BY values, they get different numbers (though the order between them is nondeterministic).

ROW_NUMBER is one of the most versatile window functions. Its most common use is the "top-N per group" pattern: partition by a grouping column, order by a ranking criterion, and then filter for row_number = 1 (or <= N). This is the standard way to get the most recent order per customer, the highest-paid employee per department, etc.

Because window functions are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT, you cannot filter on ROW_NUMBER directly in WHERE. Instead, wrap the query in a subquery or CTE and filter in the outer query.

Syntax

-- Basic row number
SELECT
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank,
  name,
  price
FROM products;

-- Row number within partitions
SELECT
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY price DESC
  ) AS category_rank,
  category,
  name,
  price
FROM products;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Most recent order per customer (top-1 per group)
SELECT customer_id, order_id, order_date, total_amount
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders o
) ranked
WHERE rn = 1
ORDER BY customer_id;

-- Top 3 best-selling products per category
SELECT category, name, units_sold
FROM (
  SELECT
    p.category,
    p.name,
    SUM(oi.quantity) AS units_sold,
    ROW_NUMBER() OVER (
      PARTITION BY p.category
      ORDER BY SUM(oi.quantity) DESC
    ) AS rn
  FROM products p
  JOIN order_items oi ON oi.product_id = p.product_id
  GROUP BY p.category, p.name
) ranked
WHERE rn <= 3
ORDER BY category, rn;

Common Pitfalls & Tips

1ROW_NUMBER with ties is nondeterministic — add a tiebreaker column (like a primary key) to the ORDER BY for reproducible results.
2You cannot use ROW_NUMBER() in WHERE directly. Wrap in a subquery or CTE: SELECT * FROM (SELECT ..., ROW_NUMBER() ...) WHERE rn = 1.
3Forgetting PARTITION BY gives you a global row number instead of per-group, which is a common mistake when writing top-N-per-group queries.

Practice ROW_NUMBER queries