39

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.

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?

0

8 Answers 8

39

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.

0
24

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
16

GREATEST and LEAST are now supported in Azure SQL, and will be supported in SQL Server 2022.

10

LEAST equivalent:

IIF(@a < @b, @a, @b)

GREATEST equivalent:

IIF(@a > @b, @a, @b)
5
  • 4
    How do you do that for three or more values, e.g. least(5,6,7,8,9)?
    – user1822
    Sep 20, 2018 at 10:42
  • @a_horse_with_no_name Use nested IIF's
    – Elnur
    Sep 20, 2018 at 13:53
  • 2
    This approach would quickly become challenging to read and verify... How does it fare in terms of performance? Oct 14, 2019 at 9:00
  • 1
    Great for numbers, not easy to read for calculated values, especially if nested.
    – alfadog67
    Apr 23, 2020 at 16:48
  • 2
    this is not equivalent of least nor greatest. Apr 18, 2021 at 10:20
7

FYI we implemented GREATEST and LEAST in Azure SQL DB and upcoming versions of SQL Server:

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=sql-server-ver15

0
5

This would be a good start -

CASE WHEN A > B THEN A ELSE B END
1
  • It's a good suggestion but it was mentioned in the question with "unrolling the condition into CASE statements" Aug 17, 2018 at 1:25
3

I was intending to add comment to @ed-avis answer, but unable to do so, due to lack of reputation, so posting this as extension to his answer.

I've eliminated disadvantage of "Annoyingly you have to make separate functions for each data type." Using SQL_VARIANT.

Here is my implementation:

CREATE OR ALTER FUNCTION my_least(@a SQL_VARIANT, @b SQL_VARIANT)
returns SQL_VARIANT
with schemabinding
as
begin
  return case when @a <= @b then @a 
              when @b < @a  then @b
              WHEN @a IS NULL THEN @b
              WHEN @b IS NULL THEN @a
              else null
         end
END;

Also this function handles NULLs like postgresql version.

This function could be added to DB for convenience, but it's 10 times slower, than using built in IIF. My tests shows, that such function with exact type (datetime) performs same as sql_variant version.

P.S. I run some tests on data-set of 350k values, and seems that performance is the same, sql_variant is tiny bit faster, but I believe it's just jitters.

But any way IIF version is 10x times faster!!!

I haven't tested inline CASE WHEN but basically for t-sql IIF is same as case, and iif get's converted by optimizer to case expression.

The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function.

CONCLUSION: It's faster to use IIF if performance matters, but for prototyping, or if code clarity is more needed, and no big calculations are involved, provided function can be used.

2
  • LEAST() and GREATEST(), when present in an SQL dialect, permit comparison within a row among n columns; I took the OP to be after a solution that would provide equivalent results. The answer here (along with several others) only supports 2. Oct 14, 2019 at 9:08
  • Thanks for that, I wasn't aware of sql_variant! I have edited my answer to incorporate your suggestion and performance results. It's strange and backwards that S.O. lets you have enough karma to post a whole new answer, but not to add a comment.
    – Ed Avis
    Nov 17, 2021 at 12:40
1

I create user-defined functions, e.g.

create function dbo.udf_LeastInt(@a int, @b int)
returns int
with schemabinding
as
begin
  return case when @a <= @b then @a 
              when @b < @a  then @b
              else null
         end
end

You can also make one using sql_variant instead of int, which will then work with any type. (Unfortunately it suffers from MSSQL's 'helpful' type conversion rules, returning nonsense if called with (1, 'a') rather than an error.) At least the performance with sql_variant does not seem any worse than the version for a particular type.

Although it may work in simple cases, there are several issues with this approach however:

  • Annoyingly you have to make separate functions for each data type.
  • It handles only 2 parameters, so one may need more functions to handle many parameters or use nested calls of the same functions.
  • It would be better (more efficient) as an inline TVF rather than a scalar function. That has to do with the implementation of scalar functions at heart. There are many blogs about it, see for example SQL 101: Parallelism Inhibitors – Scalar User Defined Functions (by John Kehayias. (However, the most recent MSSQL versions can optimize scalar functions better.)
  • On top of the problems with scalar functions and query optimization, even in a straightforward query a handwritten iif or case expression is about ten times faster than the function call.
  • If one of the arguments is null, it returns null. This matches what the least operator does in Oracle and MySQL, but differs from Postgres. But this armouring against null makes it more verbose (if you know they won't be null, a plain case when @a <= @b then @a else @b end would work).

All in all it may be better to write out the case expression longhand if performance matters. I've even resorted to generating nested case statements on the client side when there are several values to compare.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.