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.
******** it is 2020 now how can this not be implemented?! This has existed in Oracle (GREATEST/LEAST) and other DB:S for decades!
Adding a bump these exist in PostgreSQL, MySQL, MariaDB, DB2, and Oracle. Would love to see SQL Server natively support these functions!
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.
Mark Freeman commented
There are workarounds, but they are verbose, ugly, and have non-obvious performance characteristics. Adding LEAST() and GREATEST() built-in functions would be very helpful, especially if they could have more than two parameters like COALESCE().
Bob Sovers commented
I found a workaround to this functionality by using a subquery.
SELECT NAME, Score1, Score2, Score3, (SELECT MAX(s) FROM (VALUES (Score1),(Score2),(Score3)) as x(s)) as Max_Score FROM someTable;
The goal is to select the max value of multiple columns
Martin Smith commented
Accepting only 2 parameters would be quite limiting. To be useful I'd want to see it accept a variable and potentially larger number of parameters such as COALESCE does.
I believe those functions are usually named LEAST( ) and GREATEST( ) , see for example Oracle.
It would be nice to have a scalar MAX function (name it S_MAX) that returns a maximum of two numbers. Right now you have to use CASE or write your own TVF and CROSS APPLY it, both are quite clumsy. Same for minimum of two:
S_MAX(1, 0) --yields 1
,S_MIN(1, 0) --yields 0