Scalar MAX function
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:
SMAX(1, 0) --yields 1
,SMIN(1, 0) --yields 0
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.