SQL Greatest(), least()

Largest or Smallest Value of Arguments

Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite200520072009201120132015201720192021⊘ 3.5.7 - 3.43.0b⚠ 2022a⊘ 2008R2 - 2019⚠ 8.4 - 16c⚠ 8.3a✓ 11gR1 - 21c✓ 5.0 - 8.0.34✓ 5.1 - 10.11⚠ 2.0.202 - 2.1.214c⚠ 1.4.198 - 1.4.200c⚠ 1.4.191 - 1.4.197ac✓ 9.7 - 11.5.8✓ 2.0⊘ -
  1. ⚡Non-conforming null handling
  2. Alternative: scalar min and max functions
  3. Non-conforming handling of null elements in row values

The SQL functions greatest and least take one or more arguments✓✗ and return the largest or smallest value if none of the arguments is null.✓✗

  FROM …

Greatest and least are scalar functions, not aggregate functions. Unlike min and max they operate on one row at a time.

Null Treatment

The SQL standard requires greatest and least to return null in case one argument is null. This is different from aggregate functions, in particular min and max. While min and max ignore null values and return the smallest or largest value from the remaining set, greatest and least respect null values in so far as the presence of a single null value means that the highest or lowest value cannot be determined without doubt.

BigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL Serverrespect nullignore null

As you can see in the chart, the standard SQL null treatment is not always applied. This is no surprise because greatest and least were often supported decades before they were added to the SQL standard in 2023.

Personally, I think that standardizing greatest and least with strict a respect nulls semantic was not the best decision. The SQL standard is anyway not followed very strictly. Demanding new behavior that contradicts decades-old implementations0 makes it even worse because existing implementations will probably not adopt to the new standard. As both ways to treat null have been in the wild for a very long time, neither way should have become standard. It’s just another implementation-defined behavior.1

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

Going forward I hope that users will have the choice. For example, respect nulls and ignore nulls specifications could be added similar to those for some window functions.


The filter clause, known from aggregates, would be more generic but it doesn’t solve the problem of long-standing implementations that ignore null values.

Row Values

In standard SQL, a value that compares to any other value as unknown cannot be the result of greatest or least.

Applying this to the following examples means that ⓵ and ⓶ return the first value because there is no other value that could be even smaller.

⓵ LEAST( ROW(1   ,    1),  ROW(1,    2) )
⓶ LEAST( ROW(1   , null),  ROW(2, null) )
⓷ LEAST( ROW(1   , null),  ROW(1, null) )
⓸ LEAST( ROW(null,    1),  ROW(9,    2) )

If you’re wondering about ⓶, keep in mind that range comparisons on row values are fully determined as soon as a prefix gives a definite answer. This is not the case for example ⓷; even though both values are not distinct from each other, they are still not equal as per the normal comparison rules of SQL. A less-then-or-equals (<=) comparison of these two values yields unknown. The existence of an even smaller value cannot be ruled out for either of the values so that least returns the null value. The same applies to example ⓸. As the first element of one value is null, all comparisons to that value return unknown. Consequently, it is impossible that there is any value that is undoubtedly smaller-or-equal to all other values. Least returns null. These are just consequences of the general behavior of null in SQL: null represents uncertainty, uncertainty propagates.

BigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL Serverno nullprefix determines ⓶neither equal nor distinct ⓷one prefix is null

Again, it is no surprise that old systems don’t conform to the new standard. Those few tested systems that do support row values in greatest and least implement a sort-based logic—i.e., they treat null elements as largest or smallest possible values. Whether it is a very large or very small value varies among systems, very much like the default null ordering in the order by clause (ID133). Luckily, they do it the same way for greatest/least as they do it for order by.

BigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL Serverstandard conformingordered as greatest valueordered as smallest value


The SQL standard requires greatest and least to have one or more arguments. In practice, some systems do not support the (nearly useless) one-argument case. The upper bound is typically beyond my tests.

BigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL Server1 parameter2 parameters254 parameters255 parameters10 000 parameters


Some SQL dialects have scalar min and max functions that take multiple arguments and return the lowest or highest value.

Apache DerbyBigQueryDb2 (LUW)aH2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitebscalar min and max
  1. Respects null values: one null value makes the result null
  2. Respects null values: one null value makes the result null • Non-conforming handling of collations

Normative References

The greatest and least functions form the optional feature T054, “GREATEST and LEAST”.

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »


  1. The oldest non-conforming implementation I found is PostgreSQL 8.1 from 2005.

  2. Starting with SQL:2023, implementation-defined elements have an ID and a name similar to features. Part 2 has 360 of them.

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license