Unanswered: Using max with four columns
I'm stuck with a MAX query, where I need to return 4 columns form a five columned table.
The table Purchases consists of columns:
Customer ID (long int)
WareGroupNumber (a long int ID)
where the combo of waregroupnumber+subitemgroup+subitem is a unique key), the custoemr ID relates to whatever customer purchased it (foreign key), and the value is the price of the unique product purchased, BUT, a unique product doesn't have a set price, it varies from customer to customer.
What I need to get hold of, is the single most expensive product purchased by each unique customer, what the product was worth and its unique ID.
The above simply returns all the products the customer purchased and its max value, not the single most expensive product purchased.
Select Customer, WaregroupNumber, SubitemGroup, Subitem, Max(value)
GROUP BY Customer, WaregroupNumber, SubitemGroup, Subitem
Gets close to the target, but I need the product they purchased as well.
Select distinct Customer, Max(value)
GROUP BY Customer
Thanks for any help in advance,
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....