How to Select the Latest Date in SQL

When summarizing data in SQL, a common question is how to select the latest date in SQL. There are straightforward ways to get the most recent date within a table or subset of data. However, when summarizing data based on the date it is essential to understand some nuances.

Date Selection Basics

In order to select the latest date, we will use a table that has the following records. The table is called sales_records and will keep track of fields for transaction_date, transaction_amount, and customer_name.

If we simply want to get the latest date using a SQL query, we can run a query that uses the MAX() function on the transaction_date field.

SELECT MAX(transaction_date)
FROM sales_records;

When we run this query, the result that we get is: 2022-10-12 10:20:28.090. This represents the latest date in the table. That’s easy, right? But what happens if we need to include additional fields? For example, let’s say that we need to see the total transaction dollars by the customer and the date.

Summarizing Data by Latest Date in SQL

The MAX() function is an aggregation function. This means that if we add other columns that are not being aggregated, we will need to use the GROUP BY keywords for those columns.

Let’s write a query that is intended to show us how much each customer has spent with us on the most recent date that we have sales recorded. We want to get the total transaction amount for that day and by customer.

SELECT MAX(transaction_date) AS transaction_date, SUM(transaction_amount) AS total_transaction_amount, customer_name
FROM sales_records
GROUP BY customer_name;

Because we have three unique customers in the table, we will get three records returned to us. The results are not what we expect though.

If we were to manually add up the total that the customer “Kara Ford” has spent on October 12th, 2022, we would get a total transaction amount of $271.19. But the query seems to indicate that this customer spent $553.11 on that date. Why is that?

This is happening because we’re using the MAX() function on the transaction date. That means it’s going to return the most recent transaction date for this customer. We’re not actually filtering on this value, we’re simply retrieving it. The SUM() function has not been “told” to only show us results for October 12th at this point.

Filtering Queries on the Max Date

One problem we face is that the field we are using is a datetime field. This means that we’re capturing the time down to milliseconds. Since we want to summarize our results by the day, this poses a problem. Let’s see what happens if we use a subquery in our WHERE clause to get results by the maximum transaction date.

SELECT transaction_date, SUM(transaction_amount), customer_name
FROM sales_records
WHERE transaction_date = (
    SELECT MAX(transaction_date)
    FROM sales_records
)
GROUP BY customer_name, transaction_date;

When we run this query, we get only one result. We get a single record for the customer “Kara Ford” that shows a transaction date of 2022-10-12 11:12:56.947. This is because we’re getting the maximum transaction date from the table including the time stamp. In a large enterprise system, we might have transactions that happen within the same millisecond. But this still means we aren’t getting our desired result.

What we will use now is a subquery to get the maximum date formatted as YYYY-MM-DD. We will use that subquery in an INNER JOIN to join the transaction date. Here’s what that query will look like. This query is specific to Microsoft SQL Server due to the usage of the CONVERT() function. See the MySQL and PostgreSQL sections below for the queries in those respective databases.

SELECT x.max_date, customer_name, SUM(transaction_amount)
FROM sales_records s
INNER JOIN (
    SELECT CONVERT(varchar, MAX(transaction_date), 101) AS max_date
    FROM sales_records
) x ON CONVERT(varchar, transaction_date, 101) = x.max_date
GROUP BY customer_name, x.max_date;

Note here that we’re selecting the max_date field from the subquery and grouping it by that instead of using the sales_record.transaction_date field. When we run this query, we get the results we expected. There is one record for each customer

The CONVERT() function in Microsoft SQL Server allows us to convert a datetime field to a varchar and defined the formatting. The 101 format will return a date as MM/DD/YYYY. (See the MS SQL documentation for more formats.) Since this strips out the timestamp, it then allows us to perform comparisons on just the date. That allows our INNER JOIN to work effectively as a filter on the most recent transaction date in the table.

MySQL Query for Max SQL Date

In order to produce the same results in MySQL, we need to modify our query slightly. MySQL does not provide the CONVERT() function but it does give us a DATE_FORMAT() function that can be used in the same way. As you can see from the query below, this is a very slight change to get the right formatting.

SELECT x.max_date, customer_name, SUM(transaction_amount)
FROM sales_records s
INNER JOIN (
    SELECT date_format(MAX(transaction_date), "%m/%d/%Y") AS max_date
    FROM sales_records
) x ON date_format(transaction_date, "%m/%d/%Y") = x.max_date
GROUP BY customer_name, x.max_date;

You can see the values available for the formatting in the MySQL documentation.

PostgreSQL Query for Max SQL Date

The query for PostgreSQL is also a little bit different. PostgreSQL does not provide a CONVERT() or DATE_FORMAT() function. Instead, you use the TO_CHAR() function to format your date. Here is the PostgreSQL query that will accomplish the same result.

SELECT x.max_date, customer_name, SUM(transaction_amount)
FROM sales_records s
INNER JOIN (
    SELECT to_char(MAX(transaction_date), 'mm/dd/yyyy') AS max_date
    FROM sales_records
) x ON to_char(transaction_date, 'mm/dd/yyyy') = x.max_date
GROUP BY customer_name, x.max_date;

As you can see here, it is still very similar with the exception of how we format the dates. For all database platforms, the concept is the same. We format the latest date in SQL to remove the timestamp. Then we use that to select and total up our records.