Select Max query with two group by's

[kpfunf]

Tried to look at other examples, but couldn't quite get this done. I have the table Fuel with columns Fuel ID (pk), FuelType, Price, Discount, Vendor, Date. The end result is a query that shows the Fuel ID, Price, and Discount on the max date for each fuel type for each vendor (Fuel ID's are unique/autonumber; three different FuelTypes; multiple vendors, multiple dates). I've seen how to use max of date for one group by, but cannot figure out how to do that for two, or if I'm even on the right track. Thanks for the help!

[PianoMan64]

Tried to look at other examples, but couldn't quite get this done. I have the table Fuel with columns Fuel ID (pk), FuelType, Price, Discount, Vendor, Date. The end result is a query that shows the Fuel ID, Price, and Discount on the max date for each fuel type for each vendor (Fuel ID's are unique/autonumber; three different FuelTypes; multiple vendors, multiple dates). I've seen how to use max of date for one group by, but cannot figure out how to do that for two, or if I'm even on the right track. Thanks for the help!

Can you give me an example of how your data is stored in the table as well as field types for each field.

and I don't understand what you mean when you say that you want to do it on more than one date? Do you mean you want to display it for more than one day, or is there another date field in the table structure that you haven't included in you example?

Let me know by write back to my email,

hope that helps,

Joe P.

[kpfunf]

Below is sample data (very small sample):

Fuel ID FuelType Price Discount Vendor Date
297 SelfServe $5.35 $0.00 Air Wing 3/27/2008
287 AvGas $5.78 $0.00 Air Wing 3/27/2008
58 AvGas $5.46 $0.00 Air Wing 1/17/2008
59 SelfServe $5.05 $0.00 Air Wing 1/17/2008
67 AvGas $4.89 $0.20 Air Services 1/17/2008
69 JetA $5.09 $0.00 Air Services 1/17/2008
199 AvGas $4.95 $0.00 Air Services 2/28/2008
200 SelfServe $4.80 $0.00 Air Services 2/28/2008

The result would look like this:
Fuel ID FuelType Price Discount Vendor Date
297 SelfServe $5.35 $0.00 Air Wing 3/27/2008
287 AvGas $5.78 $0.00 Air Wing 3/27/2008
69 JetA $5.09 $0.00 Air Services 1/17/2008
199 AvGas $4.95 $0.00 Air Services 2/28/2008
200 SelfServe $4.80 $0.00 Air Services 2/28/2008

I need to go by date and not max FuelID in case of backdating. The "more than one date" is there could be more than one "Max Date" for each vendor because there are different fuel types (ex. Air Services).

Below is the data type for each column (FuelType and Vendor are numbered foreign keys to other tables with descriptions):
Fuel ID FuelType Price Discount Vendor Date
Autonumber Number Currency Currency Number Date/Time

[PianoMan64]

Below is sample data (very small sample):

Fuel ID FuelType Price Discount Vendor Date
297 SelfServe $5.35 $0.00 Air Wing 3/27/2008
287 AvGas $5.78 $0.00 Air Wing 3/27/2008
58 AvGas $5.46 $0.00 Air Wing 1/17/2008
59 SelfServe $5.05 $0.00 Air Wing 1/17/2008
67 AvGas $4.89 $0.20 Air Services 1/17/2008
69 JetA $5.09 $0.00 Air Services 1/17/2008
199 AvGas $4.95 $0.00 Air Services 2/28/2008
200 SelfServe $4.80 $0.00 Air Services 2/28/2008

The result would look like this:
Fuel ID FuelType Price Discount Vendor Date
297 SelfServe $5.35 $0.00 Air Wing 3/27/2008
287 AvGas $5.78 $0.00 Air Wing 3/27/2008
69 JetA $5.09 $0.00 Air Services 1/17/2008
199 AvGas $4.95 $0.00 Air Services 2/28/2008
200 SelfServe $4.80 $0.00 Air Services 2/28/2008

I need to go by date and not max FuelID in case of backdating. The "more than one date" is there could be more than one "Max Date" for each vendor because there are different fuel types (ex. Air Services).

Below is the data type for each column (FuelType and Vendor are numbered foreign keys to other tables with descriptions):
Fuel ID FuelType Price Discount Vendor Date
Autonumber Number Currency Currency Number Date/Time

Ok, I got your email and have figured out how to display this with your two groupby statements. It's simply a matter of removing from your groupby statement anything that isn't needed to evaluate the problem.

Here is the SQL Statement that you need to use:

SELECT Fuels.FuelType, Max(Fuels.Price) AS MaxOfPrice, Fuels.Vendor, Max(Fuels.Date) AS MaxOfDate
FROM Fuels
GROUP BY Fuels.FuelType, Fuels.Vendor;
 

[kpfunf]

PianoMan,
That works great. The question I now have is how to add the other data in. If I use what you wrote as a subquery, do I just join on each field to the Fuel table for a master query with all info (all columns). The unique field of FuelID, when added into the query you wrote, throws it out of whack (which makes sense; as you said, don't use things not in the evaluation). But how do I tie back without the unique field?

PS Trying to join on all fields dropped records.

[PianoMan64]

PianoMan,
That works great. The question I now have is how to add the other data in. If I use what you wrote as a subquery, do I just join on each field to the Fuel table for a master query with all info (all columns). The unique field of FuelID, when added into the query you wrote, throws it out of whack (which makes sense; as you said, don't use things not in the evaluation). But how do I tie back without the unique field?

PS Trying to join on all fields dropped records.

Still working on that part. Will get back to you.

[kpfunf]

Figured out the dropped records part: Max price. I don't want the max price, only the max date. With that max date (for each fuel type, for each vendor), I want the row's price, discount, and fuel ID.

[kpfunf]

Figured out the dropped records part: Max price. I don't want the max price, only the max date. With that max date (for each fuel type, for each vendor), I want the row's price, discount, and fuel ID.

BUMP .

[kpfunf]

Solved. Used max date query, and joined to original query on all fields.

[PianoMan64]

Solved. Used max date query, and joined to original query on all fields.

Thanks for letting me know. Happy Programming...;-)

Joe P.