SQL MAX() on date value
MAX() function on date
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 :
SELECT MAX (ord_date) AS "Max Date"
FROM orders;
Output:
Max Date --------- 20-OCT-08
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 :
SELECT ord_num, ord_amount, ord_date, agent_code
FROM orders
WHERE ord_date=(
SELECT MAX(ord_date) FROM orders WHERE agent_code='A002');
Output:
ORD_NUM ORD_AMOUNT ORD_DATE AGENT_CODE --------- ---------- --------- ---------- 200122 2500 16-SEP-08 A004 200119 4000 16-SEP-08 A010 200123 500 16-SEP-08 A002 200135 2000 16-SEP-08 A010
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 :
SELECT agent_code, MAX(ord_date) AS "Max Date"
FROM orders
GROUP BY agent_code;
Output:
AGENT_CODE Max Date ---------- --------- A004 23-SEP-08 A002 16-SEP-08 A007 20-JUN-08 A009 13-JUL-08 A011 30-JUL-08 A012 26-AUG-08 A010 16-SEP-08 A013 15-AUG-08 A001 20-OCT-08 A008 15-AUG-08 A006 20-JUL-08 A005 10-OCT-08 A003 20-JUL-08
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 :
SELECT agent_code, ord_date, cust_code
FROM orders S
WHERE ord_date=(
SELECT MAX(ord_date)
FROM orders
WHERE agent_code = S.agent_code);
Output:
AGENT_CODE ORD_DATE CUST_CODE ---------- --------- ---------- A008 15-AUG-08 C00002 A006 20-JUL-08 C00023 A010 16-SEP-08 C00007 A004 23-SEP-08 C00008 A011 30-JUL-08 C00025 A005 10-OCT-08 C00018 A001 20-OCT-08 C00014 A002 16-SEP-08 C00022 A009 13-JUL-08 C00010 A007 20-JUN-08 C00017 A006 20-JUL-08 C00024 A003 20-JUL-08 C00015 A010 16-SEP-08 C00007 A012 26-AUG-08 C00012 A013 15-AUG-08 C00013
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 :
SELECT * FROM orders
LEFT JOIN despatch
ON orders.agent_code = despatch.agent_code
WHERE orders.ord_date=(
SELECT MAX(ord_date) FROM orders);
Output:
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_C AGENT_ ORD_DESCRIPTION DES_NU DES_DATE DES_AMOUNT ORD_NUM ORD_DATE ORD_AMOUNT AGENT_CODE ---------- ---------- -------------- --------- ------ ------ ----------------------------- ------ --------- ---------- ---------- --------- ---------- ------ 200117 800 200 20-OCT-08 C00014 A001 D003 25-OCT-08 900 200117 20-OCT-08 800 A001
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition
Here is a slide presentation of all aggregate functions.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Max Having, Where, in
Next: Max Count, Having
SQL: Tips of the Day
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook