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?

up vote 25 down vote accepted

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

This would be a good start -

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

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

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.
  • 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 statement longhand if performance matters. I've even resorted to generating nested case statements on the client side when there are several values to compare.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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