MAX/MIN as NON-aggregate function
Please add the GREATEST/LEAST functions, just like in PostgreSQL/Oracle (with the same name for syntax-compatibility).
SQL-Server lacks functions for MAX/MIN as NON-aggregate function.
e.g. When I need the MAXIMUM of two values, or the minimum of two values (in columns, not rows).
When the value is a lengthy expression, it sucks having to write it in a case expression.
Also, the case-expression doesn't work for N > 2, and having to put it into a subselect-value-statement to use the aggregate functions as workaround affects readability severely (and thus maintainability).
Also, since SQL-server user-defined functions are damn slow, and on top of that don't allow a variable number of arguments, they are not a viable supplement either.
To me, adding these looks pretty easy (and low cost/complexity), and I simply don't understand why they weren't added a long time ago.
Upvotes: 8<=-=Mar 5 2017 2:42PM=-=>
Thanks for this idea. This is a valid requirement and I hope that it will get more votes. Currently we cannot confirm when it will be added, but it is in our backlog.<=-=May 22 2017 5:03AM=-=>
would like it very much, particularly since you already have the CONCAT / GREATEST() a variable number of paramenters and does something with it…<=-=Jun 5 2017 12:31PM=-=>
GREATEST / LEAST functions would be fantastic addition.<=-=Nov 14 2017 3:42PM=-=>
The workarounds using CROSS APPLY or CASE expressions are difficult to manage and read. I’d love to see these implemented.
Sandip Mehta commented
These functions will significantly reduce code size and errors.
Also, please consider adding aggregate function Product().
Easiest way to work around this is to use a derived table e.g.:
SELECT a.asset_id, a.added_on, a.changed_on
FROM (VALUES(a.added_on), (a.changed_on)) AS bothDates(EditDate)
) as lastEdit
FROM Asset a;
Not as easy as a NON-aggregate column, but will work quite efficiently. It must be noted that in some cases CASE statements may be more performant. But until this feature is added to SQL Server it is quite a cool work around.
Petr Pařík commented
Names are not so important, these do not have to necessarily be named MIN and MAX. But please implement functions with the described functionality - it will help to make code much more readable.
Also, from my point of view, variable number of arguments is very important, but can be implemented later, if it speeds up the implementation.
IMAX and IMIN would be ok as well just like IIF
Yes, I would like this with allowing an arbitrary number of arguments.