MAX(EXPR); MIN(EXPR);MAX(EXPR), MAX(ALL EXPR), AND MAX(DISTINCT EXP...

3. MAX(expr); MIN(expr);

MAX(expr), MAX(ALL expr), and MAX(DISTINCT expr) examine the values for expr

in a group of rows and return the largest value. Null values are ignored. MIN(expr),

MIN(ALL expr), and MIN(DISTINCT expr) examine the values for expr in a group of

rows and return the smallest value. Consider these queries:

Query 1: select min(commission_pct), max(commission_pct) from

employees

Query 2: select min(start_date),max(end_date) from job_history

Query 3: select min(job_id),max(job_id) from employees

Query 1 returns 0.1 and 0.4 for the minimum and maximum COMMISSION_PCT

values in the EMPLOYEES table. Notice that null values for COMMISSION_PCT are

ignored. Query 2 evaluates a DATE column and indicates that the earliest START_DATE

in the JOB_HISTORY table is 17-SEP-1987 and the latest END_DATE is 31-DEC-1999.

Query 3 returns AC_ACCOUNT and ST_MAN as the JOB_ID values appearing first and

last alphabetically in the EMPLOYEES table.

EXAM TIP There are two fundamental rules to remember when studying

group functions. First, they always operate on a single group of rows at a time.

The group may be one of many groups a dataset has been segmented into, or

it may be an entire table. The group function executes once per group. Second,

rows with nulls occurring in group columns or expressions are ignored by all

group functions, except the COUNT(*) form of the COUNT function.