Getting lower value out of 3 values in SQL SERVER

Question

How can I get the lowest out of 3 values in Sql Server?

The same function in MySQL called LEAST, and comparison in SQL Server?

Answer 1

You can do this without a nested case:

select (case when val1 < val2 and val1 < val3 then val1
             when val2 < val3 then val2
             else val3
        end) as least_of_three

This seems pretty clear as to what it is doing. It also generalizes pretty easily.

Do be careful about NULLs -- which LEAST and GREATEST ignore. If you need to handle these, then it is a bit more cumbersome:

select (case when val1 <= coalesce(val2, val1) and val1 <= coalesce(val3, val1) then val1
             when val2 <= coalesce(val3, val2) then val2
             else val3
        end) as least_of_three

Notice I've changed the "<" to "<=". I use the coalesce to "ignore" the value by evaluating to true. So, val1 is always less than val2, if val2 is null. I've chosen this method because it works for all data types (strings, numbers, dates).

EDIT:

I don't usually go back and write answers on questions seven years old, but this question is clearly SQL Server. The best approach uses apply:

select t.*, max_val
from t cross apply
     (select max(v.val) as max_val
      from (values (t.val1), (t.val2), . . .
     ) v(val);

Note that this solution does not work in MySQL because it does not support lateral joins.

Answer 2

And the obvious nested case statement would be:

select case when val1 < val2 then
               case when val1 < val3 then val1
               else val3
               end
        when val2 < val3 then val2
               else val3
    end
from cols;

on

create table cols (val1 int, val2 int, val3 int);

It's not going to scale very easily to higher # of comparisons, and it doesn't include coalesce statements to suppress nulls.

The singular advantage is that another programmer will be able to figure it out quickly when they look at your code. (And unless this is a performance bottleneck, I think that's a pretty good consideration!)