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.
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.
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.
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.
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:
Once we have the date the whole query will become like:
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).
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.
SQL Max Date
The max function in SQL allows you to fetch the greatest value from a set of values in a given column.
In most cases, we use the max function with numeric data types such as integers, floats, decimals, money, etc.
However, did you know you can use the max function with date types? Using the max() function in SQL with date data types returns the latest date.
This can be useful when filtering the recently added record in a given table.
Example Usage
Let us assume we have a table as shown below:
Check the code for the table below:
create table employees (
id serial primary key,
insert into employees (full_name, email, department, start_date, active, category)
(‘Meghan Edwards’, ‘edwards@example.com’, ‘Game Development’, ‘2016-06-22’, TRUE, ‘DEV-G1’),
(‘Sam Davis’, ‘davios@am.com’, ‘Game Development’, ‘2014-01-04’, FALSE, ‘DEV-G1’),
(‘Taylor Miller’, ‘miller@tayl.org’, ‘Game Development’, ‘2019-10-06’, TRUE, ‘DEV-G1’),
(‘Wilson Smitty’, ‘smitty_wilson@example.net’, ‘Database Developer’, ‘2016-12-23’, TRUE, ‘DEV-DB1’),
(‘Barbara Moore’, ‘moore@mail.to’, ‘Database Developer’, ‘2015-12-23’, TRUE, ‘DEV-DB1’),
(‘Raj Chan’, ‘chan.raj@mail.net’, ‘Database Developer’, ‘2017-12-23’, FALSE, ‘DEV-DB1’),
(‘Susan Brown’, ‘brown@susan.io’, ‘DevOps Engineer’, ‘2011-12-23’, TRUE, ‘DEV-OPS1’),
(‘Marial Anderson’, ‘anderson@example.org’, ‘DevOps Engineer’, ‘2015-12-23’, TRUE, ‘DEV-OPS1’),
(‘Nancy Thomas’, ‘thomas_nancy@exampl.net’, ‘DevOps Engineer’, ‘2014-12-23’, FALSE, ‘DEV-OPS1’);
We can select the latest date from the start_date column as shown:
This should return the latest date as shown:
Unfortunately, you cannot use the max(date) function with the where clause. Doing so will result in an error as shown:
This is because SQL does not allow aggregate functions in the where clause.
Inference
This article covers the basics of using the max function with a date type in SQL. For example, using the max() function with date type returns the latest date from the given set.
About the author
John Otieno
My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list
SQL MAX() on date value
In this part, you will see the usage of SQL MAX() function on date type of the column of a table.
Example:
Sample table: orders
To get the maximum ‘ord_date’ from the ‘orders’ table, the following SQL statement can be used :
SQL MAX() on date value with where
To get data of ‘ord_num’, ‘ord_amount’, ‘ord_date’, ‘agent_code’ from the ‘orders’ table with the following conditions —
1. ‘ord_date’ is equal to the maximum ‘ord_date’,
2. maximum ‘ord_date’ from those agents whose ‘agent_code’ is ‘A002’,
the following SQL statement can be used :
SQL MAX() on date with group by
To get data of ‘agent_code’ and maximum ‘ord_date’ with an user defined column alias ‘Max Date’ for each agent from the orders table with the following condition —
1. ‘agent_code’ should come in a group
the following SQL statement can be used :
SQL MAX() on date value with subquery
To get data of ‘agent_code’, ‘ord_date’ and ‘cust_code’ from the ‘orders’ table with the following conditions —
‘ord_date’ will be equal to the maximum ‘ord_date’ of ‘orders’ table with following condition —
‘agent_code’ of ‘orders’ table must be equal to the ‘agent code’ of
‘orders’ table mentioned as alias ‘S’
the following SQL statement can be used :
SQL MAX() on date value using join
Sample table: orders
Sample table: despatch
To get all columns from ‘orders’ and ‘despatch’ table after joining, with the following condition —
1. ‘ord_date’ should be largest(maximum) from the ‘orders’ table,
2. largest (maximum) ‘ord_date’ should be equal to the ‘ord_date’ of ‘ orders’ table,
3. ‘agent_code’ of ‘orders’ table should be equal to the ‘agent_code’ of ‘despatch’ table for joining,
the following SQL statement can be used :
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition
Как одним select выбрать максимальную дату в MYSQL?
Есть таблица, в которой есть тип поля datetime и timestamp. Мне нужно выбрать максимальное по времени (тип datetime) значение. Пишу :
select info, MAX(date) from table
Он мне выдает максимальную дату все ОК, но значение info от первой строки в результате выборки.
Правильно работает конструкция:
Я правильно понимаю, что только через вложенный селект можно это сделать? Т.е в одном селекте нельзя.