w3resource
ankara escort
malatya escort
SQL Tutorial

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.

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

New Exercises:R Programming