w3resource

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
   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

To get the maximum 'ord_date' from the 'orders' table, the following SQL statement can be used :


SELECT MAX(ord_date) AS "Max Date"  -- Selects the maximum value of the ord_date column and aliases it as "Max Date"
FROM orders;  -- Specifies the 'orders' table as the source of data

Explanation:

  • SELECT MAX(ord_date) AS "Max Date": This part of the query selects the maximum value of the 'ord_date' column from the 'orders' table. The MAX() function calculates the maximum value of the 'ord_date' column, which represents the latest date in the 'orders' table. The AS "Max Date" aliases the result as "Max Date", which is the name of the column in the result set.
  • FROM orders: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.

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  -- Selects the ord_num, ord_amount, ord_date, and agent_code columns
FROM orders  -- Specifies the 'orders' table as the source of data
WHERE ord_date = (  -- Filters the rows to include only those where the ord_date matches the maximum ord_date for agent_code 'A002'
    SELECT MAX(ord_date)  -- Selects the maximum value of the ord_date column for orders with agent_code 'A002'
    FROM orders  -- Specifies the 'orders' table as the source of data for the subquery
    WHERE agent_code = 'A002'  -- Filters the rows to include only those with agent_code 'A002'
);

Explanation:

  • SELECT ord_num, ord_amount, ord_date, agent_code: This part of the query selects four columns from the 'orders' table: 'ord_num', 'ord_amount', 'ord_date', and 'agent_code'. These columns will be included in the result set.
  • FROM orders: This specifies the source of the data for the main query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected.
  • WHERE ord_date = (...): This is a subquery that filters the rows to include only those where the 'ord_date' matches the maximum 'ord_date' for the agent_code 'A002'.
  • SELECT MAX(ord_date) ... WHERE agent_code = 'A002': This part of the subquery selects the maximum value of the 'ord_date' column for orders with the agent_code 'A002'. It filters the rows to include only those with the 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 :


-- This query selects the maximum order date for each agent.
SELECT 
    agent_code, -- Selecting the agent_code column from the orders table
    MAX(ord_date) AS "Max Date" -- Selecting the maximum value of ord_date column and aliasing it as "Max Date"
FROM 
    orders -- Specifying the table from which to retrieve data
GROUP BY 
    agent_code; -- Grouping the results by the agent_code column

Explanation:

  • SELECT agent_code, MAX(ord_date) AS "Max Date":
    • SELECT is used to specify the columns you want to retrieve from the table.
    • agent_code is the column that identifies the agents.
    • MAX(ord_date) calculates the maximum value of the ord_date column for each group.
    • AS "Max Date" aliases the result of MAX(ord_date) as "Max Date" for better readability.
  • FROM orders:
    • FROM specifies the table from which to retrieve data, in this case, the orders table.
  • GROUP BY agent_code:
    • GROUP BY is used to group the results based on the values of one or more columns.
    • agent_code is the column used for grouping.
    • This ensures that the maximum order date is calculated for each unique agent code separately.

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);

SELECT agent_code, ord_date, cust_code -- Selecting specific columns from the 'orders' table
FROM orders S -- Alias 'orders' table as 'S'
WHERE ord_date=( -- Filtering rows where ord_date matches the following condition
    SELECT MAX(ord_date) -- Subquery: selecting the maximum value of ord_date
    FROM orders -- From the 'orders' table
    WHERE agent_code = S.agent_code -- Filtering based on the agent_code from the outer query
);

Explanation:

  • SELECT agent_code, ord_date, cust_code: This line selects three columns (agent_code, ord_date, cust_code) from the orders table.
  • FROM orders S: This line specifies the table from which data is being selected. It aliases the orders table as S, which means we can refer to the orders table using S elsewhere in the query.
  • WHERE ord_date=(...): This line filters the rows based on a condition. It selects rows where ord_date matches the result of the subquery enclosed in parentheses.
  • SELECT MAX(ord_date): This is a subquery that selects the maximum value of the ord_date column from the orders table.
  • FROM orders: This specifies the table from which the subquery is selecting data, which is the orders table.
  • WHERE agent_code = S.agent_code: This is a condition within the subquery. It filters the rows from the orders table where the agent_code matches the agent_code from the outer query, S.

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
   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012
Sample table: despatch
DES_NUM    DES_DATE  DES_AMOUNT    ORD_NUM ORD_DATE  ORD_AMOUNT AGENT_CODE
---------- --------- ---------- ---------- --------- ---------- ----------
D002       10-JUN-08       2000     200112 30-MAY-08       2000 A007
D005       19-OCT-08       4000     200119 16-SEP-08       4000 A010
D001       12-JAN-08       3800     200113 10-JUN-08       4000 A002
D003       25-OCT-08        900     200117 20-OCT-08        800 A001
D004       20-AUG-08        450     200120 20-JUL-08        500 A002
D006       24-JUL-08       4500     200128 20-JUL-08       3500 A002

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 * -- Selecting all columns from the joined tables
FROM orders -- Selecting from the 'orders' table
LEFT JOIN despatch -- Performing a LEFT JOIN with the 'despatch' table
ON orders.agent_code = despatch.agent_code -- Joining the tables on the 'agent_code' column
WHERE orders.ord_date=( -- Filtering rows where the order date matches the following condition
    SELECT MAX(ord_date) -- Subquery: selecting the maximum value of 'ord_date'
    FROM orders -- From the 'orders' table
);

Explanation:

  • SELECT *: This line selects all columns from the result set.
  • FROM orders: This specifies the primary table from which data is being selected, which is the orders table.
  • LEFT JOIN despatch: This line specifies a left join with the despatch table. A left join returns all records from the left table (orders), and the matched records from the right table (despatch).
  • ON orders.agent_code = despatch.agent_code: This line specifies the condition for joining the two tables. It matches rows where the agent_code column in the orders table matches the agent_code column in the despatch table.
  • WHERE orders.ord_date=(...): This line filters the rows based on a condition. It selects rows where the ord_date matches the result of the subquery enclosed in parentheses.
  • SELECT MAX(ord_date): This is a subquery that selects the maximum value of the ord_date column from the orders table.
  • FROM orders: This specifies the table from which the subquery is selecting data, which is the orders table.

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql/aggregate-functions/max-date.php