CODEWITHSUNDEEP
sqlsql-servert-sql
Sam CD
Sam CD

Reputation: 2097

SELECT Maximum/Minimum of 2 or more fields

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?

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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;

Upvotes: 3

Related Questions