Calculating the minimum value in a set of columns

[philclare]

Hi,

Is there any way to select the minimum value in a set of columns in a single row.

eg. I have a table with 6 numeric fields, some of which may be null, and I want to find the lowest number in these fields for each row... I can find the first number by using COALESCE but not the lowest

Any ideas?

Thanks

Hope this helps,

Phil Clare

http://www.trinipoint.co.uk

 

[AtomicWedgie]

select min(minimum)
from
(select min(field1) as minimum from table1
union
select min(field2) as minimum from table1
union
select min(field3) as minimum from table1
union
select min(field4) as minimum from table1
union
select min(field5) as minimum from table1
union
select min(field6) as minimum from table1) minimumtbl

There must be an easier way but maybe this will work?
I would like to know if there is an easier way.

Atomic Wedgie

 

[philclare]

Well as it turns out my columns are in order so I can gut the lowest by using COALESCE (since that returns the first value that isn't null)

Thanks anyway

Hope this helps,

Phil Clare

http://www.trinipoint.co.uk