<< Click to Display Table of Contents >> Navigation:  Local SQL > Data Manipulation Language > Functions > MAX function

Returns the largest value in the specified column.

MAX([ALL] column_reference | DISTINCT column_reference)

Description

Use MAX to calculate the largest value for a value (column, literal, parameter, or caculated values) using a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, MAX returns a NULL value.

SELECT MAX(ItemsTotal)

FROM Orders

Constant values (such as literals and parameter values) can only be used with the MAX function if part of a calculation involving a table column. If MAX is applied to a calculation, the calculation is performed for each row and then the maximum value calculated based on the data as modified by the calculation.

SELECT MAX(UPPER(LastName))

FROM "Employee.db"

ALL returns the largest value for all rows. When DISTINCT is not specified, ALL is the implied default.

DISTINCT ignores duplicate values when calculating the largest value in the specified column.

MAX returns the largest value in a column or a calculation using a column performed for each row (a calculated field).

SELECT MAX(ItemsTotal), MAX(ItemsTotal * 0.0825) AS HighestTax

FROM Orders

When used with a GROUP BY clause, MAX returns one row for each group with the maximum value for that group. The statement below aggregates the largest value for the order totals column in the Orders table, producing a subtotal for each company in the Company table.

SELECT C."Company", AVG(O."ItemsTotal") AS Average,

 MAX(O."ItemsTotal") AS Biggest,

 MIN(O."ItemsTotal") AS Smallest

FROM "Customer.db" C, "Orders.db" O

WHERE (C."CustNo" = O."CustNo")

GROUP BY C."Company"

ORDER BY C."Company"

Applicability

SELECT statements. MAX can be used with all non-BLOB columns. When used with numeric columns, the return value is of the same type as the column (such as INTEGER or FLOAT). When used with a CHAR column, the largest value returned will depend on the Borland Database Engine (BDE) language driver used.

Note: the MAX function cannot be used with memo or BLOB columns.