GSquared (4/24/2008)
I'm not sure if what you're asking for is "max" and "min". Take a look at those in Books Online and see if they will do what you need.If you want to return the higher of two values in two different columns, I think a Case statement is needed.
case
when Col1 => Col2 then Col1
when Col2 > Col1 then Col2
else null
end
If the columns are nullable, then it gets complex fast.
Here is an example with 4 columns:
case
when a.Val1 is not null and
(a.Val1 >= a.Val2 or a.Val2 is null) and
(a.Val1 >= a.Val3 or a.Val3 is null) and
(a.Val1 >= a.Val4 or a.Val4 is null)
then a.Val1
when a.Val2 is not null and
(a.Val2 >= a.Val1 or a.Val1 is null) and
(a.Val2 >= a.Val3 or a.Val3 is null) and
(a.Val2 >= a.Val4 or a.Val4 is null)
then a.Val2
when a.Val3 is not null and
(a.Val3 >= a.Val1 or a.Val1 is null) and
(a.Val3 >= a.Val2 or a.Val2 is null) and
(a.Val3 >= a.Val4 or a.Val4 is null)
then a.Val3
when a.Val4 is not null and
(a.Val4 >= a.Val1 or a.Val1 is null) and
(a.Val4 >= a.Val2 or a.Val2 is null) and
(a.Val4 >= a.Val3 or a.Val3 is null)
then a.Val4
else null
end
This is an alternative that is easier to code when you have to do this for a large number of columns:
Select
[Max_of_Val1_to_Val4] =
(
select
X1= max(bb.xx)
from
(
select xx = a.Val1 where a.Val1 is not null union all
select xx = a.Val2 where a.Val2 is not null union all
select xx = a.Val3 where a.Val3 is not null union all
select xx = a.Val4 where a.Val4 is not null
) bb
)
from
MyTable a
More about these methods here:
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906
You can also use UNPIVIOT for this. Sorry, don't have an example, but that's what Books Online is for.
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx