How to Get Record with Max Value in MySQL

Many times you may need to select rows with max column value in SQL. Since there is no built-in function for it, you need to get records with max value using SQL query. Here’s how to get record with max value in MySQL. You can also use it to get rows with max value in PostgreSQL, SQL Server.

Table of Contents

How to Get Record with Max Value in MySQL

Here are the steps to get record with max value in MySQL.

Let’s say you have the following table sales(order_date,sale) that contains daily sales data.

mysql> create table sales(order_date date,sale int, orders int);

mysql> insert into sales(order_date,sale,orders) values('2020-05-29', 300, 10),
     ('2020-05-30',250, 15),( '2020-05-31', 250, 12),( '2020-06-01',250, 14),
     ('2020-06-02',150,20),('2020-06-03',300,21),('2020-06-04',200,15),
     ('2020-06-05',200,17),('2020-06-06',250,12),('2020-06-07',150,15),
     ('2020-06-08',300,12),('2020-06-09',200,18);

mysql> select * from sales;
+------------+------+--------+
| order_date | sale | orders |
+------------+------+--------+
| 2020-05-29 |  300 |     10 |
| 2020-05-30 |  250 |     15 |
| 2020-05-31 |  250 |     12 |
| 2020-06-01 |  250 |     14 |
| 2020-06-02 |  150 |     20 |
| 2020-06-03 |  300 |     21 |
| 2020-06-04 |  200 |     15 |
| 2020-06-05 |  200 |     17 |
| 2020-06-06 |  250 |     12 |
| 2020-06-07 |  150 |     15 |
| 2020-06-08 |  300 |     12 |
| 2020-06-09 |  200 |     18 |
+------------+------+--------+

There are two ways to select rows with max column value in SQL. We will look at them both.

Bonus Read : How to Get Last 12 Months Data in MySQL

How to get record with max value using SQL subquery

Here’s the SQL query to get rows with max sale value using SQL subquery.

mysql> select * from sales where sale=(select max(sale) from sales);
+------------+------+--------+
| order_date | sale | orders |
+------------+------+--------+
| 2020-05-29 |  300 |     10 |
| 2020-06-03 |  300 |     21 |
| 2020-06-08 |  300 |     12 |
+------------+------+--------+

In the above query, we first select the max value for table in subquery (in bold). Then we select those rows from original sales table where sale column value is max value. For large tables, you can improve query performance by indexing sale column.

If you want to filter data as per specific conditions, add a WHERE clause in your subquery as shown below.

mysql> select * from sales 
       where sale=(
          select max(sale) from sales
          WHERE <condition>
        );

Bonus Read : How to Update View in MySQL

How to get record with max value for each GROUP

Here are the steps to get row with max value using GROUP BY in MySQL.

Let’s say you have a table product_sales(product, order_date,sale) that contains sales data for multiple products. And you want to get records with max sale value for each product.

mysql> create table product_sales(product varchar(255),order_date date, sale int);

mysql> insert into product_sales(product,order_date, sale)
     values('A','2020-05-01',250),
     ('B','2020-05-01',350),
     ('C','2020-05-01',1250),
     ('A','2020-05-02',450),
     ('B','2020-05-02',650),
     ('C','2020-05-02',1050),
     ('A','2020-05-03',150),
     ('B','2020-05-03',250),
     ('C','2020-05-03',1850);

mysql> select * from product_sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-05-01 |  250 |
| B       | 2020-05-01 |  350 |
| C       | 2020-05-01 | 1250 |
| A       | 2020-05-02 |  450 |
| B       | 2020-05-02 |  650 |
| C       | 2020-05-02 | 1050 |
| A       | 2020-05-03 |  150 |
| B       | 2020-05-03 |  250 |
| C       | 2020-05-03 | 1850 |
+---------+------------+------+

Bonus Read : How to Get Cumulative Total Users Per Day in MySQL

Here’s the SQL query to get record with max value using GROUP BY, for each group, that is, each product.

mysql> select product_sales.* from product_sales,
      (select product,max(sale) as sale 
           from product_sales 
           group by product) max_sales 
        where product_sales.product=max_sales.product 
        and product_sales.sale=max_sales.sale;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-05-02 |  450 |
| B       | 2020-05-02 |  650 |
| C       | 2020-05-03 | 1850 |
+---------+------------+------+

In the above query, we first calculate max sale value for each product using nested subquery (in bold), and then join the result with original product_sales table based on product and sale columns.

Hopefully, you can get rows with max column value for your tables.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Fill Missing Dates in MySQL?

MySQL ADD COLUMN

How To Add NOT NULL Constraint in MySQL