MySQL

How to Fetch the Row Which Has the Max Value of a Column in MySQL

Fetch Row with Max Value

On an online shopping website, we’d like to show visitors the most recent items added to the table items, which were logged in a column called added_time in our case. In this post, I will show you two ways that can fetch the row which has the max value of the column added_time.

Subquery

There’re two queries in the statement. The inner query uses group function MAX to fetch the latest time, and the outer query uses the predicate to query the whole table.

mysql> select id, item_name, added_time from items where added_time = (select max(added_time) from items);
+---------+-------------+---------------------+
| id      | item_name   | added_time          |
+---------+-------------+---------------------+
| 6474143 | LEGO® City  | 2018-08-20 22:13:18 |
+---------+-------------+---------------------+
1 row in set (1.60 sec)

1.6 seconds was consumed in fetching the row from a table which contains over 6 million rows. Although it looked fast, we still can improve it by adding an index on this column added_time.

mysql> alter table items add index idx_for_added_time (added_time);
Query OK, 0 rows affected (3.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

We can query it again and see how fast it can be.

mysql> select id, item_name, added_time from items where added_time = (select max(added_time) from items);
+---------+-------------+---------------------+
| id      | item_name   | added_time          |
+---------+-------------+---------------------+
| 6474143 | LEGO® City  | 2018-08-20 22:13:18 |
+---------+-------------+---------------------+
1 row in set (0.00 sec)

Impressive! Now we know it’s better to have index on the column used in predicates.

Sorting

We sorted all rows by the column descendingly and then limit only one row to be returned.

mysql> select id, item_name, added_time from items order by 3 desc limit 1;
+---------+-------------+---------------------+
| id      | item_name   | added_time          |
+---------+-------------+---------------------+
| 6474143 | LEGO® City  | 2018-08-20 22:13:18 |
+---------+-------------+---------------------+
1 row in set (0.00 sec)

Hope them helpful!

Leave a Reply

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