Selcting max value with all duplicate rows

[HectorH]

Hello,



I have a table that has information polled hourly over a month period
equipment, value, day, hour as fields.

I want to return only the rows that have the max value for each equipment.

Does anyone know the SQL code? I am able to do it for the equipment and max value but I mess up when I try to include the day and time.

thanks in advance

[orange]

Please post what you have tried etc so we can understand.

If you're polling by time, where do the duplicates come from?
What is a duplicate in your terms?

[pbaldy]

Is this what you're after?

http://www.baldyweb.com/LastValue.htm

[HectorH]

PBALDY,

That is the direction I was heading on my latest attempt so Ill use yours as a guide to troubleshoot my code and will let you know if it works for me. Thanks.

[HectorH]

I got the results, by going thru the acess design after using PBALDYs suggestion. I am going to look at the SQL statement to see how to do it using subqueries Thanks.

[pbaldy]

Happy to help. It's not hard to do as a subquery, but I find it harder to maintain that way, and I often have other uses for the base query anyway.

[HectorH]

Orange,

Each day has hr1, hr2, hr3,etc

Although I got the results. I would like to have it as part of one table instead of being dependent on a query.

In addition I have some zero reads that I want yo eliminate and I dont want to modify the source table. I am more accustom to using the where statement when joining.

Pretty much I want to make a new table with just the max value for each equipment. (a month period) I do not want to have it linked to a intermediate query if possible. I know you do subqueries but I am a newbie.

[orange]

I agree with Paul that subqueries can be used. Many people still work with a query of a query.

For info on subqueries see
http://allenbrowne.com/subquery-01.html

[pbaldy]

I said you could use a subquery, but I wouldn't. They can be slower, and from the same site:

Use stacked queries instead of subqueries. Create a separate saved query for JET to execute first, and use it as an input "table" for your main query. This pre-processing is usually (but not always) faster than a subquery. Likewise, try performing aggregation in one query, and then create another query that operates on the aggregated results. This post-processing can be orders of magnitude faster than a query that tries to do everything in a single query with subqueries.

[HectorH]

thanks for everyones input. I believe I have enough to do the task and enough to get a better understanding of how to do what i want to more efficently.