How to get max on each row [modified]

[xoxoxoxoxoxox]

Hi Guys, I want to compare three columns on each row and get the maximum value. I tried using GREATEST(col1,col2,...) function but it didn't work (I hve SQL version 8.0 installed). Is there any way to get max of several colunms per row? Here is how it should look like. =========================================================== ID, measurement_01, measurement_02, measurement_03, maximum =========================================================== 1, 300, 350, 325, 350 2, 225, 275, 400, 400 3, 100, 500, 300, 500 =========================================================== I'll appreciate any help Thanks -- modified at 16:06 Friday 26th January, 2007

[andyharman]

What database-engine and front-end tool are you using?

[xoxoxoxoxoxox]

Its SQL server 2000. SQL Query Analayzer version 8.0

[andyharman]

The easiest way (assuming you only need the maximum of 2 columns) would be:

select id, measurement_01, measurement_02, measurement_03,
case
when measurement_01 >= measurement_02 and measurement_01 >= measurement_03
then measurement_01
when measurement_02 >= measurement_03
then measurement_02
else measurement_03
end as maximum
from #temp_ajh
order by id

Regards Andy

[Michael Potter]

Or you can flatten the table (as it should have been designed in the first place). SELECT m.ID, m.measurement_01, m.measurement_02, m.measurement_03, MAX (FlatTable.measurement_01) FROM MeasurementTable m INNER JOIN (SELECT ID, measurement_01 FROM MeasurementTable UNION SELECT ID, measurement_02 FROM MeasurementTable UNION SELECT ID, measurement_03 FROM MeasurementTable) AS FlatTable ON (m.ID = FlatTable.ID) GROUP BY m.ID, m.measurement_01, m.measurement_02, m.measurement_03

[xoxoxoxoxoxox]

Both solutions are perfect! Thank you very much for your answer. -- modified at 17:21 Friday 26th January, 2007