MIN

MIN evaluates the minimum of an expression over a set of rows. You can use it as an window (analytic) function.

Syntax

MIN ( [ DISTINCT | ALL ] Expression )

DISTINCT

If this qualifier is specified, duplicates are eliminated.

ALL

If this qualifier is specified, all duplicates are retained. This is the default value.

Expression

An expression that evaluates to a numeric data type: SMALLINT.

The expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery, and it must evaluate to an ANSI SQL numeric data type. This means that you can call methods that evaluate to ANSI SQL data types.

If an expression evaluates to NULL, the aggregate skips that value.

Usage

Only one DISTINCT aggregate expression per Expression is allowed. For example, the following query is not valid:

   --- Not a valid query:
SELECT COUNT (DISTINCT flying_time),
              MIN (DISTINCT miles)
 FROM Flights;

Since duplicate values do not change the computation of the minimum value, the DISTINCT and ALL qualifiers have no impact on this function.

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.

Results

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

The comparison rules for the Expression’s type determine the resulting minimum value. For example, if you supply a VARCHAR argument, the number of blank spaces at the end of the value can affect how the minimum value is evaluated: 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.

Examples

splice> SELECT MIN (BirthDate) FROM Players;
1
----------
1975-07-13

This example finds the minimum number of walks and strikeouts by any pitcher in the database:

splice> SELECT MIN(Walks) "Walks", Min(Strikeouts) "Strikeouts"
   FROM Pitching JOIN Players on Pitching.ID=Players.ID
   WHERE Position='P';
Walks |Strik&
-------------
1     |1

1 row selected

Analytic Example

The following shows the homeruns hit by all batters who hit more than 10, compared to the least number of Homeruns by a player who hit 10 or more on his team:

splice> SELECT Team, DisplayName, HomeRuns,
   MIN(HomeRuns) OVER (PARTITION BY Team ORDER BY HomeRuns
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "Least"
   FROM Players JOIN Batting ON Players.ID=Batting.ID
   WHERE HomeRuns > 10
   ORDER BY Team, HomeRuns DESC;
TEAM      |DISPLAYNAME             |HOMER&|Least
--------------------------------------------------
Cards     |Mitch Canepa            |28    |11
Cards     |Jonathan Pearlman       |17    |11
Cards     |Roger Green             |17    |11
Cards     |Michael Rastono         |13    |11
Cards     |Jack Hellman            |13    |11
Cards     |Kelly Wacherman         |11    |11
Giants    |Bob Cranker             |21    |12
Giants    |Buddy Painter           |19    |12
Giants    |Billy Bopper            |18    |12
Giants    |Mitch Duffer            |12    |12

10 rows selected

See Also