Help: maximum of four fileds - how?

[nifty]

How do you find the maximum of four fields in each record of a query. Say (for example) you have daily records of the rainfall across four cities, where the cities are the fields in the query. how do you write an extra calculated field to the query that shows the max. rainfall across the fields on a paticular day.

Many thanks if you can help
Nifty

 

[namliam]

No you redesign your DB/table. Having 4 columns for the same purpose is 99% of the time flawed design.

It would be better to have a table with
Date CityName Rainfall

Rather than
Date CityA CityB CityC CityD

To fix your immediate problem... You can make a union query... then use that as a sub query...

Something like
Select CityName, Rainfall from (
Select 'CityA' CityName, CityA rainfall from table
Union Select 'CityB', CityB from table
Union Select 'CityC', CityC from table
Union Select 'CityD', CityD from table
) group by Cityname

Add dates etc as required.

Good luck !

 

[nifty]

No you redesign your DB/table. Having 4 columns for the same purpose is 99% of the time flawed design.

It would be better to have a table with
Date CityName Rainfall

Rather than
Date CityA CityB CityC CityD

To fix your immediate problem... You can make a union query... then use that as a sub query...

Something like
Select CityName, Rainfall from (
Select 'CityA' CityName, CityA rainfall from table
Union Select 'CityB', CityB from table
Union Select 'CityC', CityC from table
Union Select 'CityD', CityD from table
) group by Cityname

Add dates etc as required.

Good luck !

Thanks nifty

 

[nifty]

Thanks for your help - good Idea.
Nifty