MIN, MAX Functions
SQL MIN() & MAX() functions are used to find the lowest value and largest value of a column respectively.
MIN(column_name): It returns the lowest value of the column.
MAX(column_name): It returns the largest value of the column.
MIN() Function Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
MAX() Function Syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
SQL MIN() and MAX() on specific column
Table: Orders
ORDER_NO ORDER_DATE CUSTOMER_ID AMOUNT PAID -------- ---------- ----------- ------ ------ 100123 10-NOV-18 70010 5000 2000 100223 15-AUG-18 70099 7000 4000 100544 17-JUL-18 70013 4000 1000 100700 20-JAN-18 70101 9000 1000 100928 24-DEC-18 70745 18000 11000
Find the minimum value order from the table Orders:
SELECT MIN(AMOUNT) FROM Orders;
Output:
MIN(AMOUNT)
-----------
4000
Find the maximum value order from the table Orders:
SELECT MAX(AMOUNT) FROM Orders;
Output:
MAX(AMOUNT)
-----------
18000
We can also use Alias while getting the minimum and maximum value from the table. For example: Lets rewrite the above SQL statement:
SELECT MAX(AMOUNT) AS LargestOrder FROM Orders;
Output:
LargestOrder
-----------
18000
SQL MIN() and MAX() with addition & subtraction of two columns
Table: Orders
ORDER_NO ORDER_DATE CUSTOMER_ID AMOUNT PAID -------- ---------- ----------- ------ ------ 100123 10-NOV-18 70010 5000 2000 100223 15-AUG-18 70099 7000 4000 100544 17-JUL-18 70013 4000 1000 100700 20-JAN-18 70101 9000 1000 100928 24-DEC-18 70745 18000 11000
Find the lowest value of (AMOUNT-PAID) from the table “Orders”:
SELECT MIN (AMOUNT - PAID) AS MIN_DUE FROM Orders;
Output:
MIN_DUE -------- 3000
Find the largest value of (AMOUNT-PAID) from the table “Orders”:
SELECT MAX (AMOUNT - PAID) AS MAX_DUE FROM Orders;
Output:
MAX_DUE -------- 8000
SQL SELECT MIN() and MAX() on date value
Table: Orders
ORDER_NO ORDER_DATE CUSTOMER_ID AMOUNT PAID -------- ---------- ----------- ------ ------ 100123 10-NOV-18 70010 5000 2000 100223 15-AUG-18 70099 7000 4000 100544 17-JUL-18 70013 4000 1000 100700 20-JAN-18 70101 9000 1000 100928 24-DEC-18 70745 18000 11000
Find oldest order date:
SELECT MIN (ORDER_DATE) AS "Oldest Order Date" FROM orders;
Output:
Oldest Order Date ----------------- 20-JAN-18
Find latest order date:
SELECT MAX (ORDER_DATE) AS "Latest Order Date" FROM orders;
Output:
Latest Order Date ----------------- 24-DEC-18