How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

Selecting rows that contain the maximum value in one column while partitioning (or grouping) by another column is a common requirement in SQL. For example, you might want to find the highest-paid employees per department or the most expensive product per category. Below are multiple approaches to achieve this in MySQL, including solutions that leverage window functions available in MySQL 8.0+ and classic approaches for older versions.

1. Using Window Functions (MySQL 8.0+)

If you’re using MySQL 8.0 or later, you can use window functions (ROW_NUMBER(), RANK(), etc.) with the PARTITION BY clause. Here’s a simplified example assuming you want to find the row with the maximum score within each category.

SELECT *
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (PARTITION BY t.category ORDER BY t.score DESC) AS rn
    FROM your_table AS t
) AS sub
WHERE sub.rn = 1;

Explanation

PARTITION BY t.category: Partitions the results by category.
ORDER BY t.score DESC: Orders each partition by score in descending order (highest first).
ROW_NUMBER() OVER (...): Assigns a row number to each row, starting at 1 for the highest score in each partition.
WHERE sub.rn = 1: Filters to only those rows with the highest score in each category.

This approach is concise, performant, and more readable than older methods.

2. Using a Self-Join or Correlated Subquery (For Older MySQL Versions)

For MySQL versions prior to 8.0, window functions aren’t supported. One workaround is to use a correlated subquery to filter rows. This can be slower for large tables, but it’s widely compatible.

2.1 Correlated Subquery Example

SELECT t1.*
FROM your_table t1
WHERE t1.score = (
    SELECT MAX(t2.score) 
    FROM your_table t2
    WHERE t2.category = t1.category
);

Logic:

For each row in t1, we look up the maximum score of rows in the same category from t2.
The condition t1.score = MAX(t2.score) ensures we only pick rows with the maximum score for their category.

2.2 Self-Join Example

You can also accomplish this by joining the table to itself based on matching categories and using the > condition:

SELECT t1.*
FROM your_table t1
LEFT JOIN your_table t2
  ON t1.category = t2.category
  AND t1.score < t2.score
WHERE t2.score IS NULL;

Logic:

If there is no t2.score greater than t1.score for the same category, it means t1.score is the maximum.
The condition t2.score IS NULL filters out rows that aren’t at the top for their category.

3. Handling Ties (Multiple Rows with the Same Maximum)

If more than one row can share the same maximum value, you might want all such rows. Here’s how you can modify each approach:

Window Function: Use RANK() instead of ROW_NUMBER(). Then filter rows where RANK() = 1.

SELECT *
FROM (
    SELECT 
        t.*,
        RANK() OVER (PARTITION BY t.category ORDER BY t.score DESC) AS rn
    FROM your_table AS t
) AS sub
WHERE sub.rn = 1;

Correlated Subquery: No change needed; the condition t1.score = (SELECT MAX(t2.score) ...) naturally returns all rows tied for the maximum.
Self-Join: Same as above; t2.score IS NULL will include all rows at the maximum.

4. Best Practices

Check Your MySQL Version: If you have MySQL 8.0 or newer, the window function approach is typically the cleanest and fastest.
Indexing: If you’re grouping by category and sorting/filtering by score, having an index on (category, score) can significantly improve performance.
Avoid SELECT * When Possible: Select only the columns you need, especially if your table has many columns or large data types.
Validate Data Volume: For very large datasets, window functions generally outperform correlated subqueries. Always test or check query execution plans.

5. Elevate Your SQL and Database Skills

If you’re preparing for interviews or simply aiming to master advanced SQL, check out these DesignGurus.io courses:

Grokking SQL for Tech Interviews
Learn real-world query optimization, advanced joins, window functions, and more.
Grokking Database Fundamentals for Tech Interviews
Dive deeper into indexing, normalization, and building scalable database designs.

6. Mock Interviews with Ex-FAANG Engineers

If you want personalized feedback on your query skills or overall technical interviews, consider Mock Interviews at DesignGurus.io. You’ll practice with ex-FAANG engineers who can help pinpoint areas of improvement, from SQL nuances to system design decisions.

Conclusion

Selecting rows with a maximum column value per group (or partition) can be done efficiently in MySQL with window functions (if on MySQL 8.0+) or with correlated subqueries or self-joins in older versions. Each method suits different versions and scenarios. By learning and applying these techniques—and by practicing advanced SQL concepts—you’ll be well-equipped to handle common data-analytics and application-development tasks in MySQL.

Happy querying, and keep exploring more in-depth database concepts through the resources at DesignGurus.io!

CONTRIBUTOR

TechGrind