Getting lower value out of 3 values in SQL SERVER
sql, sql-server, t-sql
Solution
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.
Problem
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?