SQL in a Nutshell, 3rd Edition

Name

MIN and MAX

MIN(expression) and MAX(expression) find the minimum and maximum values of expression (string, datetime, or numeric) in a set of rows. Either DISTINCT or ALL may be used with these functions, but they do not affect the result.

ANSI SQL Standard Syntax

MIN( [ALL | DISTINCT] expression )
MAX( [ALL | DISTINCT] expression )

MySQL

MySQL supports the ANSI SQL syntax of MIN and MAX. MySQL also supports the functions LEAST and GREATEST, providing the same capabilities.

Oracle

Oracle supports the ANSI SQL syntax and implements the following analytic syntax:

MIN({ALL|[DISTINCT] expression}) OVER (window_clause)
MAX({ALL|[DISTINCT] expression}) OVER (window_clause)

For an explanation of the window_clause, see the section later in this chapter titled “ANSI SQL Window Functions.”

PostgreSQL and SQL Server

These platforms support the ANSI SQL syntax of MIN and MAX.

Examples

The following query finds the best and worst sales for any title on record:

SELECT  MIN(ytd_sales), MAX(ytd_sales)
FROM    titles;

Aggregate functions are used often in the HAVING clauses of queries with GROUP BY. The following query selects all categories (types) of books that have an average price for all books in the category higher than $15.00:

SELECT  type 'Category', AVG( price ) 'Average Price'
FROM    titles
GROUP BY type
HAVING AVG(price) > 15