SQL in a Nutshell, 2nd Edition

Name

MIN and MAX

Synopsis

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

SQL2003 Syntax

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

PostgreSQL and SQL Server

These platforms support the SQL2003 syntax of MIN and MAX.

DB2 and Oracle

DB2 and Oracle support the ANSI 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 Section 4.3.

MySQL

MySQL supports the SQL2003 syntax of MIN and MAX. MySQL also supports the functions LEAST( ) and GREATEST( ), providing the same capabilities.

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 clause 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