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 :
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;
- The subquery is to retrieve all the columns along with the row numbers.
- 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.
- 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;
- In this approach, we are writing inner queries.
- In QUERY1, we retrieve the distinct name-distinct_name for each salesperson.
- 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.
- QUERY3, is the outer query for the QUERY1 and QUERY2 , which will retrieve the ids.
- 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
Thanks for reading.