Does SQL Server support GREATEST and LEAST, if not what is the common workaround?
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.
You can also put the values inline in a subquery. Like this:
select (select max(i) from (values (1), (2), (5), (1), (6)) AS T(i)) greatest,
(select min(i) from (values (1), (2), (5), (1), (6)) AS T(i)) least
LEAST equivalent:
IIF(@a < @b, @a, @b)
GREATEST equivalent:
IIF(@a > @b, @a, @b)