Select the max value of each group

Advanced: Beyond MAX and GROUP BY

The primary example shown above is great for simple use cases. But life often takes us into the wilderness of more complex data, where we need to tame various peculiarities like duplicates or fetching additional columns.

Dealing with duplicates: Using ROW_NUMBER

As some wise creature on the internet once quipped, "Too many duplicates spoil the data broth." To handle duplicates, let's add ROW_NUMBER(). Not only does this come in handy to keep our data soup seasoned to perfection, but it keeps the SQL Goblin at bay.

SELECT category, score
FROM (
    SELECT category, score,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rnk
    FROM records
) as ranked_records
WHERE rnk = 1;


Here, ROW_NUMBER() assigns a unique rank within each partition of categories, with higher scores ordering first. Simply filter for rank 1 records to get the leaders.

Maintaining row integrity: INNER JOIN with subquery

For completeness, you can INNER JOIN with a subquery. It's like constructing a Venn diagram with your data, but without the crayons and disappointment from your art teacher.

SELECT r1.category, r1.score
FROM records r1
INNER JOIN (
    SELECT category, MAX(score) AS max_score
    FROM records
    GROUP BY category
) r2 ON r1.category = r2.category AND r1.score = r2.max_score;


This approach ensures complete rows for those champion scores in each category.

Boosting query's performance, accuracy & flexibility

At times, we must deal with vast farmlands (large datasets). So, let's step up our game to make our queries powerful, accurate, and flexible like a gymnast on a power-drill.

Bonus level: Indexing & query performance

Both game developers and data gurus know that performance matters. Be brave, put your indexing hat on, speed up those queries, and plunge into the dark abyss of large data, fearlessly. Ensure judicious indexing, potentially including the PRIMARY KEY for more efficient data manipulation.

Robustness using error handling

Like players in a game, values too can behave unpredictably. They might be NULL, they could be of an unexpected data type. Keep calm, and have strategies to handle oddball values and keep your SQL sailing smooth.

Customizing with deterministic results

To maintain a consistent output sequence, additional sorting criteria may be needed. Who knew data wrangling was so much like herding cats? Add deterministic columns to ORDER BY within your ROW_NUMBER() function.