How to Use SQL MAX() Function with Dates?

Let’s learn what MAX() function is first of all.

MAX() function will give you the maximum values from all the values in a column.

Learn more about MAX and MIN in this article:

MIN, MAX, COUNT, AVG and SUM in SQL

MAX function works with “date” data types as well and it will return the maximum or the latest date from the table.

Let’s take a look at the basic MAX() example:

NOTE 

All the following examples are based on store_orders table which is available on the DEMO link provided at the end of the article.

Query:

SELECT MAX(date_order_received) AS Max_Date FROM store_orders; 

Checkout the DEMO to see the results of the query above.

The query above will return the latest date (last time) on which we have received an order.

Result:

Latest Order Received On
02/03/2019

Don’t forget to mention Alias in the query or it won’t work.

WHERE with MAX(Date)

We can use WHERE condition with MAX(date) as well.

Let’s find out details about the latest order we received from a specific customer using following query.

Query:

SELECT * FROM store_orders where date_order_received=(SELECT MAX(date_order_received) FROM store_orders WHERE customer_id=203)

We are using subquery in this example as we can’t simply use WHERE=MAX(date), that will give you an error.

So, the following subquery will give us a date on which we received an order from the specific customer:

SELECT MAX(date_order_received) FROM store_orders WHERE customer_id=203

Once we have the date the whole query will become like:

SELECT * FROM store_orders where date_order_received=DATE;

So, in the end we will get a full row with different columns about the order details.

Group BY with MAX(Date)

We can use Group BY function with MAX(Date) as well. 

We have bunch of employees in our store with employee_id. They handle different orders. Let’s assume we want to see when did each of them work last time. We can accomplish that using Group By function with Max(date).

Query

SELECT employee_id, MAX(date_order_received) AS Latest_Order FROM store_orders group by employee_id;

In the query above, we are fetching employee_id column and MAX(date) column.

As I have mentioned earlier, I am using alias for Max(date), which is “Latest_Order”.

At the end, we are using Group by to group all the rows with same employee_id values.

Share this post