Yogesh Chauhan's Blog

How to Use SQL MAX() Function with Dates?

in SQL/MySQL on 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.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
SQL ANY OperatorSQL/MySQL5 Ways to Loop Through JavaScript ArraysJavaScriptIN and BETWEEN Operators in SQLSQL/MySQLWhere is the PHP log file located on Mac OS?PHPCreate a responsive pricing table using simple HTML and CSSCSSHow to scroll contents of HTML body horizontally and vertically using JavaScript?JavaScript