SQL Functions   «Prev  Next»
Lesson 10 The MIN and MAX functions
Objective Write a query that uses several numeric functions.

MIN and MAX Functions in SQL

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.

Finding Extreme Values (MIN and MAX)

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

What is the earliest order date in the database?
SELECT MIN(ORDER_DATE)
FROM ORDERS;
MIN(ORDER_DATE)
----------------
2007-01-04

What is the best sales performance of any salesperson?
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:

Comparison of the ASCII and EBCDIC collating sequences of a list of strings
The difference in the collating sequences means that a query with an ORDER BY clause can produce different results on two different systems.

SQL Numeric Functions - Exercise

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