Select the Most Recent Record by Date in SQL

When working in an SQL database, you might come across an instance where you need to retrieve the most recent record form a given table based on the date. This can be for pagination, inventory management, monitoring purposes, etc.

In this guide, we will walk you through the various methods and techniques that we can use to select the most recent record from a table based on the date.

Sample Data

For demonstration purposes, we use the Sakila sample database that is available for MySQL and PostgreSQL flavors.

Feel free to download and import the sample database on your server. You can also use any other dataset as appropriate.

Example 1: ORDER BY

The most basic and simplest method that we can use to retrieve the most recent record by date is to use an SQL ORDER BY clause.

We can order the records in a descending order based on the date value and then limit the result to just one row.

Take for example the rental table from the Sakila sample database. It contains the “rental_date” column which denotes the date at which a film was rented.

We can use this to demonstrate how to use the ORDER BY clause to retrieve the most recent record from the table.

SELECT *

FROM rental

ORDER BY rental_date DESC

LIMIT 1;

In this case, we use the ORDER BY clause and pass the “rental_date” as the target column. We also ensure to tell the database to order the records in descending order.

Finally, we also limit the number of output records which should return the most recent row from the table.

Example 2: Using the Max() Function

Did you know that we can use the max() function on date values? Yes, we can use a simple SQL subquery and the max() function on date values to retrieve the most recent record from a given table.

Consider the following example:

SELECT *

FROM rental

WHERE rental_date = (SELECT MAX(rental_date) FROM rental);

Using the subquery finds the maximum rental date from the table. In the main query, we should fetch the records with a “rental_date” equal to the maximum date.

Example 3: Window Functions

For databases that support the window functions, we can use a subquery and row_number() function to retrieve the most recent record from the table as follows:

SELECT *

FROM (

SELECT *,

ROW_NUMBER() OVER (ORDER BY rental_date DESC) AS rn

FROM rental

) AS subquery

WHERE rn = 1;

In the given example, the subquery assigns a row number to each row based on the “rental_date” column in descending order using the ROW_NUMBER() window function.

The outer query then selects all columns from the subquery where the row number is 1, effectively selecting the most recent rental record(s).

Conclusion

In this post, we explored the various methods and techniques that we can use to fetch the most recent record based on a date.