How to Remove Duplicate Rows in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
Assign ROW_NUMBER() over the columns that define a duplicate, then keep only rows where it equals 1. QUALIFY does this filter without a wrapping subquery.
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;Writing the deduplicated result back
To persist the deduplicated set instead of just reading it, wrap the same logic in CREATE OR REPLACE TABLE.
CREATE OR REPLACE TABLE analytics.orders_deduped AS
SELECT * EXCEPT(row_num)
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS row_num
FROM orders
)
WHERE row_num = 1;For an exact full-row duplicate check regardless of key, a plain GROUP BY on every column also removes duplicates: grouping alone collapses matching rows down to one, no HAVING needed. To find which rows were duplicated instead of removing them, add HAVING COUNT(*) > 1. ROW_NUMBER with QUALIFY is still the more flexible option when you need a specific 'latest' row per key rather than an arbitrary one.
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