O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required