YogeshChauhan . com

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.

dreamhost

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 uninstall Cocoapods from the Mac OS? #4 How to add Read More Read Less Button using JavaScript? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

May 5 Use inline if to make a shorter conditional syntax in React May 4 What is Conditional Rendering in React? May 3 How does @extend rule work in SCSS (Sass)? May 2 How different is Handling Events in React vs HTML DOM? May 2 How to create bouncing balls using HTML canvas and JavaScript? Apr 30 HTML canvas methods

You might also like these

Revisiting variable scope in JavaScriptJavaScriptHow to CREATE TABLE in SQL with and without using Another Table?SQL/MySQLRendering Elements in ReactReactOpen Source Security Tools for Defense SecurityMiscWhat are CSS Specificity Rules and how does browser apply them?CSSSteps to Secure a VPNMisc