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