MySQL select row with max value

A lot of times, we need to get the rows that have maximum value in a column. There can be different requirements around the same, and hence there are various solutions.

In this article, we will look into how to select only rows with maximum value on a column using,

MySQL select the row with maximum value in a column : MAX() function

This section will help us learn how to get the maximum value for a column and get the record details corresponding to it. Let us start by creating a table sales_details followed by inserting some records to it. ( Read MySQL INSERT INTO for more information on inserting rows to tables).

# Create the table sales_details
CREATE TABLE sales_details (
    sale_person_id INT,
    sale_person_name VARCHAR(255),
    commission_percentage INT,
    no_products_sold INT,
    total_commission INT
);
# Insert the records
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission) 
VALUES(1,"Aditi",10,200,2000);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission) 
VALUES(2,"Furan",5,300,1500); 
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission) 
VALUES(3,"Veronica",10,250,2500);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission) 
VALUES(4,"Atharv",25,150,3750); 
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission) 
VALUES(5,"Erick",20,350,7000); 
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission) 
VALUES(6,"Rasmus",25,355,8750); 

Run a select query on sales_details to view the records (see figure1.1).

SELECT * FROM sales_details; 

Output:-

figure 1.1

Now: Get the name, commission percentage, and the number of products sold for the salesperson who made the maximum sales.

Below is the code to achieve results to the above ask.

SELECT 
    sale_person_name, commission_percentage, no_products_sold
FROM
    sales_details
WHERE
    no_products_sold = (SELECT 
            MAX(no_products_sold)
        FROM
            sales_details);

Output:-

figure 1.2

Approach : The approach is simple to get the maximum sales in a subquery using the MAX() function and get other details from sales_details table corresponding to the maximum sales.

So, here we got the sales person name – Rasmus who did the maximum sales that is 355.

In case there is more than one salesperson who made the maximum sales, we will get all records in the results retrieved by the same select query. Let’s assume that in the table sales_details there was more than one person who sold the maximum number of products 350 (see figure 1.3).

figure 1.3

Here, the output of the same select query that we ran above will be.

figure 1.4

In case we want only the first record to be displayed in the results, we can use the LIMIT clause to get only one result. See the below query and output(figure 1.5).

SELECT 
    sale_person_name, commission_percentage, no_products_sold
FROM
    sales_details
WHERE
    no_products_sold = (SELECT 
            MAX(no_products_sold)
        FROM
            sales_details)
LIMIT 1;

Output:-

figure 1.5

As we can see, we got only one result because we limited them to 1.

MySQL select the row with maximum value in a column : LEFT JOIN

Another approach to get the row with maximum value in a particular column is using the joins. Again, we will be writing a select query using JOINS to get the name, commission percentage, number of products sold for the salesperson who sold the maximum number of products from sales_details table.

 SELECT 
    s1.sale_person_name, s1.commission_percentage , s1.no_products_sold
FROM
    sales_details s1
        LEFT JOIN
    sales_details s2 ON s1.no_products_sold < s2.no_products_sold
WHERE
    s2.sale_person_name IS NULL;

Output :

figure 1.6

Approach: The approach here is to join the table with a JOIN condition that the left side value less than the right side value. When we do this, the row(s) on the right side with max value will have NULL on the other columns. Hence, we will then select only the records on the right side, where sale_person_name is NULL.

Note: We can limit the results to 1 if we want by using the LIMIT clause.

We hope that you are able to resolve your queries taking reference from this article. Learn how to get the rows that have maximum value in a column per group.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top