Does SQL Server support GREATEST and LEAST, if not what is the common workaround? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

sql-server add tag

evan carroll (imported from SE)

Reviewing [this question](https://dba.stackexchange.com/q/186906/2639) it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use `greatest` and `least`..

    least(extendDate,min), greatest(extendDate,max)

When I try to use these though, I get

    'least' is not a recognized built-in function name.
    'greatest' is not a recognized built-in function name.

That would cover extension in either direction. 

For the purposes of the question, you would still have to do exclusive range replacement.

I'm just wondering how SQL Server users implement query patterns to mimic `least` and `greatest` functionality. 

* [PostgreSQL `GREATEST`/`LEAST`](https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-GREATEST-LEAST)
* [MySQL `GREATEST`/`LEAST`](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html)
* MariaDB [`GREATEST`](https://mariadb.com/kb/en/library/greatest/) [`LEAST`](https://mariadb.com/kb/en/library/least/)
* DB2 [`GREATEST`](https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0052623.html) [`LEAST`](https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0052624.html)
* Oracle [`GREATEST`](https://docs.oracle.com/database/121/SQLRF/functions078.htm#SQLRF00645) [`LEAST`](https://docs.oracle.com/database/121/SQLRF/functions099.htm#SQLRF00657)

Do you unroll the conditions into `CASE` statements or is there an extension, third party add-on, or license from Microsoft that enables this functionality?

Top Answer

meme (imported from SE)

One common method is to use the `VALUES` clause, and `CROSS APPLY` the two columns aliased as a single column, then get the `MIN` and `MAX` of each.

    SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest
    FROM   dbo.Users AS u
    CROSS APPLY ( VALUES ( u.CreationDate ), ( u.LastAccessDate )) AS x ( CombinedDate );

There are other ways of writing it, for example using `UNION ALL`

    SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest
    FROM   dbo.Users AS u
    CROSS APPLY ( SELECT u.CreationDate UNION ALL SELECT u.LastAccessDate ) AS x(CombinedDate);


However, the resulting [query plans][1] seem to be the same.

  [1]: https://www.brentozar.com/pastetheplan/?id=r1JWjFYiZ

1 Answer