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