February 18, 2003 at 8:12 am
I want to write and inline select statement that produces the largest value of 3 columns.
For instance, I have columns int1, int2, int3 in table1. Int1 is larger than int2, which is larger than int3, so the value I want is int1
I either need a simple Case statement or is there a predefined function that you can use to return the largest of the list?
February 18, 2003 at 9:02 am
Never mind, I wrote my own. Here's the code if you're interested, although it'd be interesting to know if there is such a function to do this automatically. The code below sets the value to 1 if all 3 are 0.
select case when int1 >= int2 and int1 >= int3 and int1 <> 0 then int1
when int2 >= int1 and int2 >= int3 and int2 <> 0 then int2
when int3 >= int1 and int3 >= int2 and int3 <> 0 then int3
else 1
end as 'QTY'
from table1
February 18, 2003 at 1:24 pm
Another way you could try is like so but not sure what the total cost may be compared to the way you listed.
SELECT MAX(IVal)
FROM
(
SELECT int1 AS IVal in table1
UNION ALL
SELECT int2 AS IVal in table1
UNION ALL
SELECT int3 AS IVal in table1
) as prequery
To combine with multiple data output you can add the other columns and group on them as needed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply