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

Reviewing this question 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

• MySQL GREATEST/LEAST

• MariaDB GREATEST LEAST

• DB2 GREATEST LEAST

• Oracle GREATEST LEAST

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?

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 seem to be the same.

SQL Server now supports both of these functions in Azure SQL DB, and presumably they will be available on prem for SQL Server 2022.

StackExchange Database Administrators Q#187090, answer score: 39