ROW_NUMBER
Assign a unique sequential number to each row within a partition, in a defined order. The workhorse window function — powers top-N-per-group and "latest row per user" dedupe. Deterministic only if ORDER BY has a tie-breaker.
Syntax #
sql
SELECT key_col, ts_col, metric,
ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY ts_col DESC) AS rn
FROM table_name;Example #
Loading SQL editor...
Warning
Always tie-break your ORDER BY. When two rows share the ordering key, ROW_NUMBER can assign 1-and-2 in either order between runs — so "top customer" flips randomly across reruns of the same report. Add a stable secondary sort (usually a surrogate id): ORDER BY revenue DESC, id.