How to Use ROW_NUMBER in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
ROW_NUMBER() OVER (PARTITION BY group ORDER BY sort_col) numbers rows 1, 2, 3 within each group with no ties. Pair it with QUALIFY to keep the first row per key, deduplicate, or build top-N lists.
SELECT
customer_id,
order_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_number
FROM orders;Latest row per key
The most common use: sort each partition newest-first, keep row 1. QUALIFY makes it a single query.
SELECT *
FROM user_snapshots
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY snapshot_at DESC) = 1;ROW_NUMBER vs RANK vs DENSE_RANK
ROW_NUMBER breaks ties arbitrarily (two equal values still get different numbers - and which row gets which number can change between runs unless the ORDER BY is unique). RANK gives ties the same number and then skips (1, 1, 3); DENSE_RANK gives ties the same number without skipping (1, 1, 2). Use RANK or DENSE_RANK when ties should be treated equally, and make the ORDER BY deterministic when they should not.
SELECT
product,
revenue,
ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
RANK() OVER (ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rnk
FROM product_revenue;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free