CASE expression with MIN / MAX

A CASE expression with MIN / MAX has the following syntax:

---

case_expression ::= { MIN | MAX }( expression1,expression2, ..., expressionn )

---

expression

Expression that returns an alphanumeric, national, numeric or time value when evaluated.
It cannot be a multiple value with a dimension greater than 1.

The values of expression1,expression2,...,expressionn must have compatible data types (see section "Compatibility between data types").

A CASE expression with MIN or MAX references different expressions. In this way it differs from the aggregate functions MIN() and MAX() (see "Aggregate functions") which reference the set of all values in a column in a table.

Result

The result of the CASE expression is NULL if at least one of the expressions contained in the parentheses (expression1,expression2,...,expressionn) returns NULL.

If no expression returns NULL, the result of the CASE expression is the value of the smallest expression when MIN is specified, the value of the largest expression when MAX is specified.

The CASE expression MIN(expression1,expression2) corresponds to a CASE expression with a search condition in the following form:

CASE 
   WHEN expression1 <= expression2 THEN expression1 
   ELSE expression2 
END

The CASE expression MIN(expression1,expression2,...,expressionn) corresponds to the CASE expression

MIN(MIN(expression1,expression2,...),expressionn).

The CASE expression MAX(expression1,expression2) corresponds to a CASE expression with a search condition in the following form:

CASE 
   WHEN expression1 >= expression2 THEN expression1 
   ELSE expression2 
END

The CASE expression MAX(expression1,expression2,...,expressionn) corresponds to the CASE expression

MAX(MIN(expression1,expression2,...),expressionn).

Example

The example below selects all entries in the turnover table since the date entered with the user variable input_date, but at most for the last 90 says.

SELECT * FROM turnover WHERE turnover.date >= MAX(:input_date,

DATE_OF_JULIAN_DAY(JULIAN_DAY_OF_DATE(CURRENT_DATE) - 90))