How Can I SELECT Rows with MAX( Column value), PARTITION by Another Column in MYSQL?

In this post, we will learn How can I SELECT rows with MAX (Column value), and PARTITION by another column in MYSQL which we can perform using the row number function to select the rows that have the highest values in a column and in addition to that it has a division too and that too with another column. For the same, we have given an example too.

Row with Max Value

Let’s take an example of the table given below where we have a table here

Suppose you have a table named as which is given below “employees” with columns named “employee_id”, “department_id”, and “salary”.  Where we need to select the employees with the highest salary in each department.

SELECT employee _id, department _id, salary
FROM (
    SELECT employee _id, department _id, salary,
           ROW_ NUMBER() OVER (PARTITION BY department_ id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

This query uses a subquery with the row number() function to assign a rank to each employee’s salary within their department, ordered in descending order. The outer query then selects only the rows with the highest rank (for example, the highest salary) for each department.

Note that this query uses the row number function, which is available in the latest versions like  MySQL 8.0 and later versions. If you are using an earlier version of MySQL, you can achieve the same result using a self-join or a correlated subquery.

And in addition to that, we can also get the same result as we had above and done with the work as per our choice by simply following the code given below.

SELECT e. employee_ id, e.department_ id, e. salary
FROM employees e
WHERE e. salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_ id = e. department_ id
);