How to Select Most Recent Record in MySQL

In this tutorial, we aim at exploring how to select the most recent record in MySQL.

While understanding user behavior or performing exploratory data analysis on time-series datasets, filtering data based on the entry timestamp becomes critically important. This entry timestamp is stored in MySQL in a particular format.

This format can be illustrated as yyyy-mm-dd HH:MM:SS. In most businesses, while trying to debug data packets related issues, it becomes necessary to access one of the most recent records in the table.

MySQL helps us perform this operation using the MAX() method. Let us understand how this method works.

Before we begin, we must create a dummy dataset by creating a table, student_details.

-- create the table Student_Registration
CREATE TABLE Student_Registration
    (
        sample_id int NOT NULL,
        sample_name VARCHAR(20),
        sample_ts TIMESTAMP
    );
    
-- insert rows to the table Student_Registration
INSERT INTO Student_Registration
    (
        sample_id, sample_name, sample_ts
    )VALUES
    (1, 'Preet S', '2016-01-01 00:00:01'),
    (2, 'Dhruv M', '2017-01-01 00:00:01'),
    (3, 'Peter P', '2018-01-01 00:00:01'),
    (4, 'Martin G', '2019-01-01 00:00:01'); 

The above query creates a table with rows a sample_id, sample_name, and a registration timestamp as sample_ts. To view the entries in the data, we use the following code.

SELECT * FROM Student_Registration;

Output:

sample_id	sample_name		sample_ts
1			Preet S			2016-01-01 00:00:01
2			Dhruv M			2017-01-01 00:00:01
3			Peter P			2018-01-01 00:00:01
4			Martin G		2019-01-01 00:00:01

Let us fetch the sample_ts of the most recent student’s registration. We can achieve this using the sample_ts column.

Select Most Recent Record in MySQL

The following query can help us fetch the student with the most recent entry in the sample_ts column.

SELECT   
 MAX(sample_ts) AS most_recent_registration
FROM Student_Registration;



Output:

most_recent_registration
2019-01-01 00:00:01

Thus, as we can see in the above code block, we have access to the most recent timestamp entry with the help of the sample_ts column. An alternative to this technique would be using the ORDER BY DESC clause in MySQL and limiting the value to 1.

It can be understood in greater depth with the following query.

SELECT *
FROM   Student_Registration
ORDER  BY sample_ts DESC
LIMIT  1;

The code above would fetch us all the columns associated with the most recent record.

Output:

sample_id	sample_name		sample_ts
4			Martin G		2019-01-01 00:00:01

Therefore, with the help of the MAX function or the ORDER BY DESC clause alongside a timestamp column, we can efficiently select the most recent record from a table in MySQL.