ROW_NUMBER()
Syntax, PARTITION BY, pagination, deduplication, and latest row per group.
Read guideWhat are window functions?
Window functions calculate a value across related rows without collapsing the result into one row per group. ORDER BY inside OVER() defines the window order, and PARTITION BY restarts the calculation for every group.
SELECT customer_id,
created_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS row_number
FROM orders;Choose the function by the result you need
| Function | Use it when | Ties |
|---|---|---|
| ROW_NUMBER() | You need a unique number per row, pagination, or the latest row per group. | Broken arbitrarily unless you add a tiebreaker. |
| RANK() | You want to preserve the position consumed by a tie. | Creates gaps. |
| DENSE_RANK() | You want ranking without gaps. | No gaps. |
| LAG() / LEAD() | You need to compare a row with the previous or next row. | — |
RANK() vs DENSE_RANK()
Understand ties and choose the correct ranking function.
Read guideLAG() and LEAD()
Compare previous and next values in time series.
Read guideWhen the query does not behave as expected
Use EXPLAIN ANALYZE to compare estimates and actual rows before changing indexes or parameters. To observe regressions and production patterns, monitor query behavior over time.