YogeshChauhan.com
How to Use SQL MAX() Function with Dates?
December 29, 2019


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.

dreamhost

Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?



Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?



You might also like these

Debugging in WordPress Part 3: SCRIPT_DEBUG and SAVEQUERIESWordPressA Quick Guide to Object-Oriented Programming in PHPPHPHow to Install PHP Laravel in MacOS Catalina?PHPSteps to Secure a VPNMiscURL paths in DrupalDrupalSolution for Xcode 11 Command PhaseScriptExecution failed with a nonzero exit code errorMisc