SQL-J Language Reference
Page 76 of 121

MAX

MAX is an aggregate function that evaluates the maximum of the expression over a set of values (see Aggregates (Set Functions)). MAX is allowed only on expressions that evaluate to built-in data types (including CHAR, VARCHAR, DATE, TIME, BIT, etc.) or orderable Java data types.

Syntax

MAX ( [ DISTINCT | ALL ] Expression )

The DISTINCT qualifier eliminates duplicates. The ALL qualifier retains duplicates. These qualifiers have no effect in a MAX expression. Only one DISTINCT aggregate expression per SelectExpression is allowed. For example, the following query is not allowed:

SELECT COUNT (DISTINCT flying_time), MAX (DISTINCT miles)
FROM Flights

The Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. It must evaluate to a built-in data type or to a Java data type that is automatically mapped to a built-in data type. You can therefore call methods that evaluate to built-in data types. (For example, a method that returns a java.lang.Integer or int evaluates to an INTEGER.) If an expression evaluates to NULL, the aggregate skips that value.

For CHAR, VARCHAR, and LONG VARCHAR, the number of blank spaces at the end of the value may affect how MAX is evaluated. For example, if the values `z' and `z ' are both stored in a column, you cannot control which one will be returned as the maximum, because a blank space has no value.

The resulting data type is the same as the expression on which it operates (it will never overflow).

MAX Examples

-- find the latest date in the FlightBookings table
SELECT MAX (travel_date)
FROM FlightBookings

-- find the longest flight originating from each airport,
-- but only when the longest flight is over 10 hours
SELECT MAX(flying_time), orig_airport
FROM Flights
GROUP BY orig_airport
HAVING MAX(flying_time) > 10

-- use MAX on a method invocation
SELECT MAX (customized_tour.getTotalCost().doubleValue())
FROM CustomizedTours

-- use MAX on an orderable Java data type
SELECT MAX(city) FROM Cities