MIN MAX Functions in SQL

The MIN() and MAX() functions in SQL are used to find the smallest and largest values in a column, respectively. If you have a table named students and you wish to find the minimum and maximum StudentId, you can use these functions as follows:

SELECT
    MIN(StudentId) AS MinimumId,
    MAX(StudentId) AS MaximumId
FROM students;

In this query, MIN(StudentId) will return the smallest StudentId value, and MAX(StudentId) will return the largest StudentId value. The AS keyword is used to provide alias names MinimumId and MaximumId for these columns, to make the output more readable.
Please note, MIN() and MAX() functions can be used with numeric, string, date, and time data types. For numeric data, they return the minimum and maximum values. For string data, they return the minimum and maximum string values according to the ASCII value. For date and time data, they return the earliest and latest date or time. Lastly, always check your SQL platform's documentation to ensure correct usage as the implementation can slightly differ across SQL dialects.

As the names suggest, the MIN and MAX functions will return the minimum and maximum values for a particular column. All you need to do is include the name of the column you want to query in parentheses. The engine will read through the information and determine the appropriate values to satisfy your query. Here is an example:

SELECT MyName, MyAddress, MyNumber, 
     MIN(MyNumber), 
     MAX(MyNumber) FROM MyTable

You will get two new, unnamed columns back from the query. The first will contain the lowest value in the column, and the second will contain the highest. The same rules apply to all these functions, if you use the GROUP BY clause in your SELECT statement, MIN/MAX will apply to those values within the group.

The MIN() and MAX() column functions find the smallest and largest values in a column, respectively. The data in the column can contain numeric, string, or date/time information. The result of the MIN() or MAX() function has exactly the same data type as the data in the column. Here are some examples that show the use of these column functions: What are the smallest and largest assigned quotas?

SELECT MIN(QUOTA), MAX(QUOTA)
FROM SALESREPS ;
------------ ------------
$200,000.00 $350,000.00

SELECT MIN(ORDER_DATE)
FROM ORDERS;
MIN(ORDER_DATE)
----------------
2007-01-04

SELECT MAX(100 * (SALES/QUOTA))
FROM SALESREPS;
MAX(100*(SALES/QUOTA))
-----------------------
135.44

When the MIN() and MAX() column functions are applied to numeric data, SQL compares the numbers in algebraic order (large negative numbers are less than small negative numbers, which are less than zero, which is less than all positive numbers). Dates are compared sequentially. (Earlier dates are smaller than later ones.) Durations are compared based on their length. (Shorter durations are smaller than longer ones.) When using MIN() and MAX() with string data, the comparison of two strings depends on the character set being used. On a personal computer or a typical server, both of which use the ASCII character set, digits come before the letters in the sorting sequence, and all of the uppercase characters come before all of the lowercase characters.
On mainframes, which use the EBCDIC character set, the lowercase characters precede the uppercase characters, and digits come after the letters. Here is a comparison of the ASCII and EBCDIC collating sequences of a list of strings, from smallest to largest:

Try this exercise to test your knowledge of numeric functions.
SQL Numeric Functions - Exercise