aggregate query

[brent78]

I'm trying to create a query that finds the max of a group of records and then displays all details about that record. Suppose this is my data:

Type Size Color Quantity
A L Green 5
A M Blue 7
B S Orange 2
B M Red 4
B L Purple 5
C M Red 10
C L Green 12

How can I get output as
Results:
Type Size Color Quantity
A M Blue 7
B L Purple 5
C L Green 12

I can get the max items with my one sort field ok, but if I want to add more fields it starts grouping them and making duplicates.

Any suggestions,

thanks

[Rabbit]

Will there be any duplicate colors at the Type level?

Is there some sort of primary key?

[brent78]

Will there be any duplicate colors at the Type level?

Is there some sort of primary key?

Yes, there can be duplicate colors and there is a primary key as shirt_id

[Rabbit]

Yes, there can be duplicate colors and there is a primary key as shirt_id

Put this in the criteria for shirt_id:

(SELECT TOP 1 shirt_id FROM [Table Name] WHERE Type = x.Type ORDER BY DESCENDING Quantity;)

You'll also have to give the table an alias of "x".