4 fields, all numbers need to select max...

[christheprogrammer]

Hi,
I have a table with many fields, 4 of which are lengths of fish (different types of measurement for the same fish). I need to find the maximum value for the length for each record (not each column as MAX() does) and update another table with this value. Any ideas?
Thanks a bunch

Chris Chris
grandin1@yahoo.com

 

[redlam]

Hmmm... I dunno. Maybe a case statement?
How bout something like this:

select fish_id,
case
when length1 > length2 and length1 > length3 and length1 > length4 then length1
when length2 > length1 and length2 > length3 and length2 > length4 then length2
when length3 > length1 and length3 > length2 and length3 > length4 then length3
when length4 > length1 and length4 > length2 and length4 > length3 then length4
else
0
end as 'MaxLength'
from fish

 

[christheprogrammer]

Thanks, I think that will do it!!!!!
Cheers Chris
grandin1@yahoo.com

 

[hirick]

Better yet, why not normalize your data? Create a new table, with fields FishId, MeasurementType, and FishLength.
(MeasurementType will identify the 4 columns you used in your current table).

Then all you need is a simple update query with a select max(FishLength).

 

[ludmart]

This will give the same results, but may be easier to expand if more lengths must be compared. I dont know about performance.

select
fish_id,
MaxLen = ( select max(AnyLen) from
( select AnyLen = length1 union
select length2 union
select length3 union
select length4 )
T1 )
from fish

 

[tlbroadbent]

ludmarts's suggestion is good but the suggested query is incorrect and would return invalid results. Try this one.

Select
f.Fish_ID,
MaxLen=max(f.AnyLen)
From
(
Select Fish_ID, AnyLen = length1 From fish
Union
Select Fish_ID, length2 From fish
Union
Select Fish_ID, length3 From fish
Union
Select Fish_ID, length4 From fish
) As f

Group By f.Fish_ID

As ludmart suggested, the performance hit might be bad if the table is large. However, the hit will be significantly less with this revised query than with the query he suggsted. Adding Where criteria should aid performance as will matching to another table for the update. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost