Mysql: select rows with MAX(Column value), DISTINCT by another column

We have a requirement to select the rows with maximum value in one column, DISTINCT, by another column in a table. In this article, we will be looking into the same using :

• ROW_NUMBER() Function and PARTITION BY
• INNER JOIN and LIMIT

Let us start by looking into the data. Assume that we have a table named sale_details with the below rows.

Let’s look into different solutions.

Using ROW_NUMBER() function and PARTITION BY

MySQL introduced ROW_NUMBER() function 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.

To get the distinct sale_person_name having the maximum value for no_products_sold. Observe the below query and output (figure 1.2).

SELECT sd.id,
  sd.sale_person_name,
  sd.no_products_sold,
  sd.sales_department
  FROM (
  SELECT 
  id,
  sale_person_name,
  no_products_sold,
  sales_department,
  ROW_NUMBER() OVER(PARTITION BY sale_person_name ORDER BY no_products_sold DESC) toprow
  FROM sale_details
  ) sd 
  WHERE sd.toprow =1;

Output:-

Approach:-

1. The subquery is to retrieve all the columns along with the row numbers.
2. Row_Number() function will partition the rows into separate sets ordered by no_products_sold in descending order where the top row has the maximum no_products_sold.
3. Finally, the outer query will select all the required columns on the subquery results filtered by pulling only the top row.

Even if there are rows with the same values of no_products_sold for the same sales_person_name, the above solution will still function. Assume that the data in the below table changes to.

The above query with row_number() function will give the same results as shown below (figure 1.4).

Using INNER JOIN and LIMIT

We need to get the distinct sale_person_name having the maximum value for no_products_sold. Observe the below query and output.

SELECT 
    id, sale_person_name, no_products_sold, sales_department
FROM
    (SELECT                               # QUERY3
        (SELECT                           # QUERY2
                    id
                FROM
                    sale_details sd1
                WHERE
                    sd1.sale_person_name = distinct_name.sale_person_name
                ORDER BY sd1.no_products_sold DESC
                LIMIT 1) limitedId. 
    FROM
        (SELECT DISTINCT                  # QUERY1
        sale_person_name
    FROM
        sale_details)     
        distinct_name) sd2,  # QUERY3
    sale_details sd3
WHERE
    sd3.id = sd2.limitedId; 

Output :-

Approach :-

1. In this approach, we are writing inner queries.
2. In QUERY1, we retrieve the distinct name-distinct_name for each salesperson.
3. In QUERY2, we retrieve the single id-LimitedId, for the salesperson from the record they made the maximum sales, filtering with the names matching from QUERY1.
4. QUERY3, is the outer query for the QUERY1 and QUERY2 , which will retrieve the ids.
5. Finally, selecting the required columns in the outer query by joining ids from QUERY3 and sale_details table.

In case there is more than one row in the table with equal values for no_products_sold for a single salesperson, this solution will work there as well.

Assume the data of sale_details table to be :

Rerunning the same select query will give us the same output because we limit the Ids retrieved using the LIMIT keyword.

We hope this article helped you with your queries. Do read articles related to selecting rows with maximum value on a column.

MySQL select row with max value
MySQL select row with max value for each group