MySQL select row with max value for each group

While working with databases, we get many situations where we need to get records with max value for each group of grouped SQL results. While there are many solutions to it, we will be discussing a few of them.

Table of Contents

• GROUP BY : MySQL select row with max value
• JOIN : MySQL select row with max value
• ROW_NUMBER() : MySQL select row with max value
• MAX() : MySQL select row with max value

Before moving into the different solutions, let us first create the table sales_department_details, followed by inserting a few records to make our data. (Learn more on how to INSERT data – MySQL INSERT INTO).

# create a table sales_department_details
CREATE TABLE sales_department_details (
    sale_person_id INT ,
    sale_person_name VARCHAR(255),
    commission_percentage INT,
    no_products_sold INT,
    sales_department VARCHAR(255)
);
# Insert to table sales_department_details
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(1,"Henry",10,200,"Kichten Essentials");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(1,"Henry",20,400,"Apperals");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(1,"Henry",50,40,"Medicines");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(2,"Richa",12,300,"Kichten Essentials");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(2,"Richa",20,500,"Apperals");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(2,"Richa",50,50,"Medicines");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(3,"Ved",22,100,"Kichten Essentials");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(3,"Ved",20,150,"Apperals");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(3,"Ved",60,10,"Medicines");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(4,"George",34,600,"Kichten Essentials");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(4,"George",20,1200,"Apperals");
INSERT INTO sales_department_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,sales_department) 
VALUES(4,"George",20,60,"Medicines");

We will now run the select * query on sales_department_details to view the records.

SELECT * FROM sales_department_details;

Output:-

GROUP BY : MySQL select row with max value

Let’s say we want to retrieve the name along with the maximum sales of each salesperson. Below is the select query

SELECT sale_person_name, MAX(no_products_sold) AS number_products_sold 
FROM sales_department_details
GROUP BY sale_person_name ;

Output :-

Approach: The approach here is simple to get the sale_person_name and the maximum no_products_sold grouped by sale_person_name.

If we want to retrieve the entire row from the sales_department_details table, then, in that case, this solution is not appropriate as we will get an error “SELECT list is not in GROUP BY clause and contains nonaggregated column…”. 

Assume the requirement is to get the entire row’s data for the salesperson corresponding to the maximum sales. There is a pretty simple solution to it. Let’s observe the below code and its output (refer figure 1.3).

SELECT 
    *
FROM
    sales_department_details
WHERE
    no_products_sold IN (SELECT 
            MAX(no_products_sold)
        FROM
            sales_department_details
        GROUP BY sale_person_name);

Output:-

Approach :

1. Run the subquery to get only the maximum no_products_sold group by sale_person_name.
2. Select all the details from the sales_department_details table filtered with a condition to match the no_products_sold retrieved in the subquery.

JOIN : MySQL select row with max value

Again, the requirement is to get the entire row’s data for the salesperson corresponding to the maximum sales. Another solution is to use JOINS. Let’s observe the below query and the output (refer figure1.4)

SELECT 
    sd1.sale_person_id,
    sd1.sale_person_name,
    sd1.no_products_sold,
    sd1.commission_percentage,
    sd1.sales_department
FROM
    sales_department_details sd1
        INNER JOIN
    (SELECT 
        sale_person_id, MAX(no_products_sold) AS no_products_sold
    FROM
        sales_department_details
    GROUP BY sale_person_id) sd2 
ON sd1.sale_person_id = sd2.sale_person_id
AND sd1.no_products_sold = sd2.no_products_sold;

Output:-

Approach :- As we can see, the output is the same, but the approach is to :

1. Run a subquery to get the sale_person_id and the maximum no_products_sold group by sale_person_id.
2. Do an inner join of the sales_department_details table with the subquery results.
3. The inner join conditions match ON both the columns of the subquery results.

ROW_NUMBER() : MySQL select row with max value

Let us have a basic understanding of Row_Number() and PARTITION BY. 

MySQL introduced ROW_NUMBER() in version 8.0. This function assigns a sequential number to each row, beginning with one. 

PARTITION BY is a clause to break the entire rows into smaller sections; these partitions are set up by valid expressions formed by GROUP BY / ORDER BY clause.

We will now see how to get all the salesperson details corresponding to the department where he made maximum sales.

 SELECT sd.*
  FROM (
  SELECT 
  sale_person_id,
  sale_person_name,
  no_products_sold,
  commission_percentage,
  sales_department,
  ROW_NUMBER() OVER(PARTITION BY sale_person_id ORDER BY no_products_sold DESC) rowNumber
  FROM sales_department_details
  )sd 
  WHERE sd.rowNumber =1;

Output :-

Approach :- Run the subquery to retrieve all the columns along with rowNumber. This rowNumber is a sequential value assigned to each row by partitioning the rows into sets ordered by no_products_sold in descending order. The top row has the maximum no_products_sold—finally, doing a select * on subquery results filtered by pulling only the top row.

What if George had an equal number of products sold in more than one department, which is also the maximum number of products sold?

This solution of the ROW_NUMBER() function will not be applicable there as we should be getting both the records for George’s sales. In that case, we will use the MAX() function instead of the ROW_NUMBER(). Let us see how to do the same in the next section.

MAX() : MySQL select row with max value

Let us assume that the table sales_department_details has the data where George made his maximum sales in two departments—shown in figure 1.6.

To get all those records for each salesperson where they made the maximum sales, we can run the below query (refer figure 1.7 for output)

SELECT 
    sd.sale_person_id,
    sd.sale_person_name,
    sd.no_products_sold,
    sd.commission_percentage,
    sd.sales_department  
  FROM
    (
    SELECT 
  sale_person_id,
  sale_person_name,
  commission_percentage,
  no_products_sold ,
  sales_department,
  MAX(no_products_sold) OVER (PARTITION BY sale_person_id) as max_products_sold
  FROM sales_department_details
    ) sd
  WHERE sd.no_products_sold = sd.max_products_sold;

Output:-

Approach :-

• Run the subquery to retrieve all the columns from table sales_department_details along with an additional column max_products_sold.
• This new column gets the maximum no_products_sold from the partitioned sets PARTITIONED BY by sale_person_id using MAX() function.
• Select the desired columns from subquery results into the outer query and filter them, matching the no_products_sold with the new column max_products_sold.

We hope this article will help you provide solutions to your requirement regarding selecting rows with maximum columns corresponding to groups. Happy Coding !!