MIN function

<< LTRIM function | MONTH function >>

Derby Reference Manual

134

FROM Flights
GROUP BY orig_airport
HAVING MAX(flying_time) > 10

MIN function

MIN is an aggregate function 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, etc.).

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), MIN (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. 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 minimum value. For CHAR and VARCHAR,
the number of blank spaces at the end of the value can affect how MIN 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 minimum, because blank spaces are ignored for character
comparisons.

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

Examples

-- NOT valid:
SELECT DISTINCT flying_time, MIN(DISTINCT miles) from Flights
-- valid:
SELECT COUNT(DISTINCT flying_time), MIN(DISTINCT miles) from Flights
-- find the earliest date:
SELECT MIN (flight_date) FROM FlightAvailability;

MINUTE function

The MINUTE function returns the minute part of a value.

The argument must be a time, timestamp, or a valid character string representation of a
time or timestamp that is not a CLOB, LONG VARCHAR, or XML value. The result of the
function is an integer between 0 and 59. If the argument can be null, the result can be
null; if the argument is null, the result is the null value.

Syntax

MINUTE ( expression )

Example

Select all rows from the "flights" table where the "departure_time" is between 6:00 and
6:30 AM: