SQL in a Nutshell

Name

MIN and MAX

Synopsis

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

MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.

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' = MIN(ytd_sales), 'MAX' = 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
                     

Scalar Functions

Scalar functions fall into the categories listed in Table 4.2.

Table 4-2. Categories of Scalar Functions

Function Category	Explanation
Built-in	Performs operations on values or settings built into the database. Oracle uses the term “built-in” to describe all the specialty functions that are provided by Oracle, and thus “built into” their DBMS. This is a distinct and separate usage from the built-in functions described here.
Date & Time	Performs operations on datetime fields and returns values in datetime format.
Numeric	Performs operations on numeric values and returns numeric values.
String	Performs ...