SQL: Select Rows with Max Value per Group

A common problem in SQL: how to select the complete rows that contain the maximum value of a column for each group? Not just the maximum value, but the entire row.

The Problem¶

Given a table with id and rev (revision), we want to get the complete row with the highest rev for each id.

Solution 1: Subquery with IN¶

SELECT * 
FROM YourTable 
WHERE (id, rev) IN (
    SELECT id, MAX(rev)
    FROM YourTable
    GROUP BY id
)

Pros: Simple and readable Cons: Can be slow on large tables

Solution 2: Join with Subquery¶

SELECT a.*
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) as maxrev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.maxrev

Pros: Better performance than IN Cons: More verbose

Solution 3: Window Functions (MySQL 8+)¶

SELECT *
FROM (
    SELECT id, rev, 
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_order
    FROM YourTable
) a
WHERE a.ranked_order = 1

Pros: More efficient for large datasets Cons: Requires modern SQL versions

Performance Considerations¶

• Window functions are usually the most efficient
• Joins balance readability and performance
• Subqueries with IN are the simplest but less scalable

When to Use Each?¶

• Subquery: Small tables, simple code
• Join: Balance between performance and compatibility
• Window functions: Large datasets, modern SQL available

The most robust and modern pattern is window functions, which also allow handling ties and edge cases more elegantly.

Original source: Stack Overflow