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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
@mixin and @include in SCSS (Sass)SCSSA short basic guide on states in ReactReactWhat does it mean by Continuous Integration, Continuous Delivery and Continuous Deployment?MiscellaneousHow to use a Subquery to Insert Multiple Rows in SQL Table?SQL/MySQLHow to import MySQL small sample database into phpMySQL?SQL/MySQLHow to convert an HTML radio buttons into a toggle switch using CSS?CSS