ROW_NUMBER
ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.
Overview
ROW_NUMBER() is a window function that numbers the rows returned by a query, starting at 1, according to the ordering specified in its OVER clause. Unlike RANK() or DENSE_RANK(), it never produces ties — even rows with identical values in the ORDER BY expression get distinct, arbitrary-but-deterministic numbers based on row order.
Copy code
SELECT
customer_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders;
Common uses
- Deduplication: keep only the first row per group (e.g., the latest order per customer) by filtering
rn = 1. - Pagination: number all rows in a result set and slice a page with a range filter.
- Top-N per group: combine with
PARTITION BYto get, say, the top 3 highest-value orders per customer.
A classic dedup pattern:
Copy code
SELECT * EXCLUDE (rn)
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
)
WHERE rn = 1;
DuckDB specifics
DuckDB supports the full window function syntax needed for ROW_NUMBER(), and it also supports QUALIFY, which removes the need for a wrapping subquery entirely:
Copy code
SELECT * EXCLUDE (rn)
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders)
QUALIFY rn = 1;
Because ROW_NUMBER() requires no arguments — only an OVER clause — it's one of the simplest window functions to reach for whenever you need stable, unique row identifiers within groups, without altering the underlying table.
Related terms
The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.
row group →A row group is a fundamental storage concept in columnar databases like DuckDB that represents a horizontal partition of data containing a fixed number of…
QUALIFY →QUALIFY filters rows based on the result of a window function, letting you write conditions like 'keep only the top-ranked row per group' without wrapping the query in a subquery.
PARTITION BY →PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.
RANK and DENSE_RANK →RANK() and DENSE_RANK() are window functions that assign ranking numbers to rows within a partition, differing in how they handle ties and the gaps left afterward.
window functions →Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…
FAQS
ROW_NUMBER() always assigns strictly increasing, unique integers with no ties, even when ORDER BY values are equal. RANK() gives the same rank to tied rows and then skips subsequent rank values.
Use ROW_NUMBER() OVER (PARTITION BY
