SELECT Maximum/Minimum of 2 or more fields

Question

Is there any shortcut function that is the equivalent of:

CASE
    WHEN FieldA > FieldB
    THEN FieldA
    WHEN FieldA < FieldB
    THEN FieldB    
END

Obviously, this one is not difficult to write, but say you had more comparisons to make, i.e.

CASE
    WHEN FieldA > FieldB
    AND FieldA > FieldC
    AND FieldA > FieldD
    THEN FieldA
    WHEN FieldA < FieldB
    AND FieldC < FieldB
    AND FieldD < FieldB
    THEN FieldB
    ...
END

it would get very cumbersome. I understand that a UDF could be created to handle this, or you could do something like:

SELECT MAX(Field) FROM
(
SELECT FieldA AS Field
FROM Table
UNION ALL
SELECT FieldB AS Field
FROM Table
UNION ALL
SELECT FieldC AS Field
FROM Table
UNION ALL
SELECT FieldD AS Field
FROM Table
)

but is there any shortcut in TSQL for this? Out of curiosity, do other DBMS languages have a built-in function like this?

Possible duplicate of [Getting the minimum of two values in sql](http://stackoverflow.com/questions/1947753/getting-the-minimum-of-two-values-in-sql) — MrTux, Nov 19 '15 at 16:10
You can insert all the fileds on a @ or # temporary table and then pick the maximum or minium values. — Jaime García Pérez, Nov 19 '15 at 16:10
@MrTux I mentioned the answers given in that thread here in the question, I am just looking to see if there is a more efficient way or, as mentioned in an answer below, it could be added to TSQL, especially if it exists in other languages — Sam Cohen-Devries, Nov 19 '15 at 16:13

score 3 · Accepted Answer · answered Nov 19 '15 at 16:09

What you seem to want is greatest() and least(). Most databases provide these very useful functions. We can lobby Microsoft to add them.

In the meantime, there is a quirky use of apply that can solve this problem:

select t.*, maxcol
from t cross apply
     (select max(col) as maxcol
      from (values (fieldA), (fieldB), (fieldC)
           ) v(col)
     ) m;

SELECT Maximum/Minimum of 2 or more fields

1 Answers1