In SQL,
- The
MAX()
function returns the maximum value of a column. - The
MIN()
function returns the minimum value of a column.
SQL MAX() Function
The syntax of the SQL MAX()
function is:
SELECT MAX(columnn)
FROM table;
Here,
column
is the name of the column you want to filtertable
is the name of the table to fetch the data from
For example,
SELECT MAX(age)
FROM Customers;
Here, the SQL command returns the largest value from the age column.
![SQL MAX() example How to use MAX() in SQL](/sites/tutorial2program/files/sql-max.png)
SQL MIN() Function
The syntax of the SQL MIN()
function is:
SELECT MIN(columnn)
FROM table;
Here,
column
is the name of the column you want to filtertable
is the name of the table to fetch the data from
For Example,
SELECT MIN(age)
FROM Customers;
Here, the SQL command returns the smallest value from the age column.
![SQL MIN() example How to use MIN() in SQL](/sites/tutorial2program/files/sql-min.png)
Aliases With MAX() and MIN()
In the above examples, the field names in the result sets were MIN(age)
and MAX(age)
.
It is also possible to give custom names to these fields using the AS
keyword. For example,
-- use max_age as an alias for the maximum age
SELECT MAX(age) AS max_age
FROM Customers;
Here, the field name MAX(age)
is replaced with max_age in the result set.
![SQL MAX() example with alias How to use alias in SQL with MAX or MIN function](/sites/tutorial2program/files/sql-max-alias.png)
MAX() and MIN() With Strings
The MAX()
and MIN()
functions also work with texts. For example,
--select the minimum value of first_name from Customers
SELECT MIN(first_name) AS min_first_name
FROM Customers;
Here, the SQL command selects the minimum value of first_name
based on the dictionary order.
![SQL MAX() example with alias How to use MIN or MAX with String in SQL](/sites/tutorial2program/files/sql-min-and-max-with-string.png)
MAX() and MIN() in Nested SELECT
As we know, the MAX()
function returns the maximum value. Similarly, the MIN()
function returns the minimum value.
However, if we want to select the whole row containing that value, we can use the nested SELECT
statement like this.
-- MIN() function in a nested SELECT statement
SELECT *
FROM Customers
WHERE age = (
SELECT MIN(age)
FROM Customers
);
Here, the SQL command selects all the customers with the smallest age.
![Nested MIN in SQL How to find all rows that have min or max specified value](/sites/tutorial2program/files/sql-min-nested.png)
More SQL MAX() and MIN() Examples
We can also find the highest or lowest value from two or more values using the MAX()
and MIN()
function. For example,
-- returns 50 as highest
SELECT MAX(20, 30, 50) as highest;
-- returns 20 as lowest
SELECT MIN(20, 30, 50) as lowest;
Let's see how we can use MAX()
and MIN()
function with HAVING
:
SELECT *
FROM Customers
GROUP BY country
HAVING MAX(age);
Here, the SQL command returns the maximum age
in each country from the Customers table.
To learn more, visit SQL HAVING Clause.
Also Read: