| # Licensed to the Apache Software Foundation (ASF) under one | |
| # or more contributor license agreements. See the NOTICE file | |
| # distributed with this work for additional information | |
| # regarding copyright ownership. The ASF licenses this file | |
| # to you under the Apache License, Version 2.0 (the | |
| # "License"); you may not use this file except in compliance | |
| # with the License. You may obtain a copy of the License at | |
| # | |
| # http://www.apache.org/licenses/LICENSE-2.0 | |
| # | |
| # Unless required by applicable law or agreed to in writing, | |
| # software distributed under the License is distributed on an | |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY | |
| # KIND, either express or implied. See the License for the | |
| # specific language governing permissions and limitations | |
| # under the License | |
| name: LEAST | |
| summary: Returns the least of its arguments. | |
| syntax: | |
| sql: LEAST(value1 [, value2]*) | |
| java: call("LEAST", $("value1"), $("value2")) | |
| python: call("LEAST", col("value1"), col("value2")) | |
| arguments: | |
| - name: value1, value2, ... | |
| type: Any comparable type, such as a number, a string or a date. All arguments must have a common type | |
| required: true | |
| description: The values to compare. | |
| returns: | |
| description: The least argument. | |
| nulls: Returns NULL if any argument is NULL. | |
| type: The common type of all arguments, such as `BIGINT` for `INT` and `BIGINT`. Nullable if any argument is nullable. | |
| notes: | |
| - Strings compare character by character, so upper-case letters come before lower-case ones. | |
| - For `DOUBLE` and `FLOAT`, `NaN` is greater than every other value. | |
| see_also: | |
| - {name: GREATEST, ref: conditional/greatest} | |
| - {name: MIN, ref: aggregate} | |
| examples: | |
| - title: Compare columns of a row | |
| description: Return the smallest of three columns. | |
| sql: | | |
| SELECT a, b, c, LEAST(a, b, c) AS r | |
| FROM (VALUES (3, 7, 5), (9, 2, 4)) AS t(a, b, c); | |
| result: | | |
| | a | b | c | r | | |
| |---|---|---|---| | |
| | 3 | 7 | 5 | 3 | | |
| | 9 | 2 | 4 | 2 | | |
| - title: Mixed numeric types | |
| description: An `INT` and a `DECIMAL` argument give a `DECIMAL` result. | |
| sql: | | |
| SELECT LEAST(1, 2.5) AS r, TYPEOF(LEAST(1, 2.5)) AS r_type; | |
| result: | | |
| | r | r_type | | |
| |-----|-------------------------| | |
| | 1.0 | DECIMAL(11, 1) NOT NULL | | |
| - title: Strings | |
| description: Upper-case letters sort before lower-case ones, so `'Banana'` is less than `'apple'`. | |
| sql: | | |
| SELECT LEAST('apple', 'Banana') AS r; | |
| result: | | |
| | r | | |
| |--------| | |
| | Banana | | |
| - title: A NULL argument | |
| description: Any NULL argument returns NULL. | |
| sql: | | |
| SELECT LEAST(1, CAST(NULL AS INT), 3) AS r; | |
| result: | | |
| | r | | |
| |------| | |
| | NULL | |