Get Max Value AND Date it Occurred

[gostroff]

Hi,

I have a lot of sample records done on different dates. It's easy to
get the maximum value for any sample parameter for any sample location,
but what if I want the date it was found?

For example, if I have a thousand locations and 20 parameters measured
at each loocation, and this sampling went on every day for a year, I
want to get the maximum value for each parameter at each location, AND
I want the data on which the maximum was found. Can I do that with one
SQL call? I can't get it.
I'm happy to write the query in SQL if need be.

Cheers,
Gary Ostroff

[Bas Cost Budde]

go******@hydroqual.com wrote:

Hi,

I have a lot of sample records done on different dates. It's easy to
get the maximum value for any sample parameter for any sample location,
but what if I want the date it was found?

For example, if I have a thousand locations and 20 parameters measured
at each loocation, and this sampling went on every day for a year, I
want to get the maximum value for each parameter at each location, AND
I want the data on which the maximum was found. Can I do that with one
SQL call? I can't get it.
I'm happy to write the query in SQL if need be.

I certainly don't do this in one query. Anybody please teach me how.

Create a query that groups by parameter/location, and takes the max of
the date

Use this query in a join to the original table, linking location,
parameter name, and maxofdate, respectively, to location, parameter
name, and date -- of course --

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea