How to SELECT Rows With MAX PARTITION By Another Column in MySQL

MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes Structured Query Language (SQL) to interact with databases, making it a popular choice for web applications and various software systems. MySQL’s versatility, reliability, and ease of use make it a preferred solution for developers and organizations seeking efficient data management capabilities.

In this article, you will learn about, How can SELECT rows with MAX(Column value), and PARTITION by another column in MySQL.

Select in MySQL

The SELECT statement is used to retrieve data from one or more tables in a database. It’s one of the most fundamental and commonly used SQL commands.

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC
LIMIT 10;

consider the following database,

Select in MySQL database

The query to display the information in the database:

SELECT * FROM employees;

Output:

example -1 output

Example 1:

SELECT employee_id, first_name, last_name, salary 
FROM employees 
WHERE salary > 57000;

Output:

Example-2 output

Explanation: The SQL query retrieves employee details (employee_id, first_name, last_name, salary) from the “employees” table where the salary is greater than 57000. The output includes records of employees earning a salary higher than 57000.

MAX in MySQL

The MAX() function is an aggregate function used to return the maximum value of a column from a set of rows. It takes a single argument, which is typically the column you want to find the maximum value for.

Syntax:

MAX(expression)

Creating a database to perform some examples for better understanding:

Max in MySQL database

SELECT MAX(salary) AS max_salary FROM employees;

Output:

example-1 output

Explanation:

The SQL query calculates and outputs the maximum salary (max_salary) from the “employees” table, resulting in a value of 62000, representing the highest salary in the dataset.

Example : Finding Maximum Salary in Sales Department

SELECT MAX(salary) AS max_salary_in_sales FROM employees WHERE department = 'Sales';

Output:

example-2 output

Explanation: The SQL query retrieves the maximum salary (max_salary_in_sales) from the “employees” table for those working in the ‘Sales‘ department, resulting in an output of 59000.

Partition By in MySQL

The PARTITION BY clause is used in conjunction with window functions to divide the result set into partitions to which the function is applied separately. Each partition represents a subset of rows based on the values of one or more columns specified in the PARTITION BY clause.

In MySQL, the PARTITION BY clause is not directly used in the context of a simple SELECT statement like it is in some other database systems such as PostgreSQL or SQL Server. However, it’s commonly used in the context of window functions or analytical functions.

Syntax:

SELECT
    column1,
    column2,
    ...
    window_function(column3) OVER (PARTITION BY partition_column)
FROM
    table_name;

Example

consider the above database,

SELECT
    department,
    employee_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary_per_department
FROM
    employees;

Output:

Partition by in MySQL database output

Now, when we have learnt what is select and max in mysql , let’s see

How to SELECT Rows

To solve above , we have three approaches:

• Subquery with INNER JOIN
• Correlated Subquery
• Window Function (available in MySQL 8.0 and later)

Subquery with INNER JOIN

INNER JOIN and subqueries are powerful ways to get related data under certain conditions. In this article, we will look at how to use INNER QUIRES with subqueries in SQL.

Syntax:

SELECT t1.*
FROM your_table t1
INNER JOIN (
    SELECT partition_column, MAX(value_column) AS max_value
    FROM your_table
    GROUP BY partition_column
) t2 ON t1.partition_column = t2.partition_column AND t1.value_column = t2.max_value;

Example:

SELECT t1.*
FROM students t1
INNER JOIN (
    SELECT class, MAX(score) AS max_score
    FROM students
    GROUP BY class
) t2 ON t1.class = t2.class AND t1.score = t2.max_score;

Output:

Subquery with INNER JOIN output

Explanation: The SQL query retrieves records from the “students” table where each record represents the student with the highest score (max_score) in their respective classes. The result includes details such as class, student ID, name, and score.

Correlated Subquery

Syntax:

SELECT t1.*
FROM your_table t1
WHERE value_column = (
    SELECT MAX(value_column)
    FROM your_table t2
    WHERE t1.partition_column = t2.partition_column
);

Example:

SELECT t1.*
FROM students t1
WHERE score = (
    SELECT MAX(score)
    FROM students t2
    WHERE t1.class = t2.class
);

Output:

Correlated Subquery output

Explanation: The SQL query selects records from the “students” table where each student has the maximum score in their respective class, resulting in a list of top-scoring students in each class.

Window Function

Syntax:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY value_column DESC) AS row_num
    FROM your_table
) ranked
WHERE row_num = 1;

Example:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS row_num
    FROM students
) ranked
WHERE row_num = 1;

Output:

Window Function output

Explanation: The SQL query retrieves records from the “students” table, showcasing the students with the highest scores in their respective classes.

Conclusion

In conclusion, when selecting rows with the maximum value of a column partitioned by another column in MySQL, various approaches can be employed, including subqueries with inner joins, correlated subqueries, and window functions. Each approach offers its own advantages and considerations. Subqueries with inner joins provide a straightforward solution and are suitable for versions of MySQL prior to 8.0. Correlated subqueries offer simplicity but may be less efficient for larger datasets. Window functions, available in MySQL 8.0 and later, offer a powerful and efficient way to achieve the desired result, especially when dealing with complex analytical queries. The choice of approach should consider factors such as database schema, data distribution, and MySQL version.