SQL: Select the Latest Record Per Group (3 Methods Compared)
Get the most recent row for each group in SQL. Compares ROW_NUMBER(), correlated subquery, and DISTINCT ON approaches across MySQL, PostgreSQL, and BigQuery.
The Problem
You have a table with multiple records per entity (e.g., orders per customer, logins per user) and you need only the most recent one for each. This is one of the most frequently asked SQL questions on Stack Overflow.
Method 1: ROW_NUMBER() Window Function (Recommended)
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) SELECT * FROM ranked WHERE rn = 1;
Works in MySQL 8.0+, PostgreSQL, BigQuery, Snowflake, SQL Server. Clean, readable, and handles ties predictably.
Method 2: Correlated Subquery (MySQL 5.7)
SELECT o.* FROM orders o WHERE o.order_date = (SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id);
Works everywhere but can return multiple rows if two orders share the same max date. Slower on large tables (O(n²) without index).
Method 3: DISTINCT ON (PostgreSQL Only)
SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC;
PostgreSQL-exclusive syntax. Very concise but not portable to other databases.
Performance Tip
Add a composite index on (customer_id, order_date DESC) to make all three methods fast. The ROW_NUMBER() approach scales best on tables with millions of rows.