SQL-J Language Reference
Page 77 of 121

MIN

MIN is an aggregate expression that evaluates the minimum of an expression over a set of rows (see Aggregates (Set Functions)). MIN 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

MIN ( [ DISTINCT | ALL ] Expression )

The DISTINCT and ALL qualifiers eliminate or retain duplicates, but these qualifiers have no effect in a MIN 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.

The type's comparison rules determine the maximum value. For CHAR, VARCHAR, and LONG VARCHAR, the number of blank spaces at the end of the value may affect the result.

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

MIN Examples

-- find the earliest date
SELECT MIN (travel_date)
FROM FlightBookings

-- find the shortest flight originating from each airport,
-- but only when the airport's shortest flight is over 5 hours
SELECT MIN(flying_time), orig_airport
FROM Flights
GROUP BY orig_airport
HAVING MIN(flying_time) > 5

-- use MIN on a method invocation
SELECT MIN (city.showTemperature())
FROM Cities JOIN Countries USING (country_ISO_code)
WHERE region = 'North America'

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