SQL to return highest weight factor records only. 1

[RSfromCO]

I have a query that selects CustomerID, ProductID, and a WeightFactor for each product...

Customer Product WeightFactor
1 5 2.3
1 7 5.0
1 10 1.2
2 5 2.3
2 10 1.2
3 1 0.2

I want to return a set of results that tells me the highest weighted product number that each customer purchased...

Customer Product
1 7
2 5
3 1

I have a Customers table, a Product table (that includes ProductID and WeightFactor), and a Purchases table that includes CustomerID and ProductID (to tell me which customers purchased what products).

I'm sure this can be done through SQL, but I can't seem to find the magic syntax to accomplish this. Any help?

 

[PHV]

I have a query that selects CustomerID, ProductID, and a WeightFactor for each product
Say this query is named qryWeightFactor.
You may consider something like this:
SELECT A.Customer, A.Product
FROM qryWeightFactor AS A INNER JOIN (
SELECT Customer, Max(WeightFactor) AS MaxWeight FROM qryWeightFactor GROUP BY Customer
) AS B ON A.Customer = B.Customer AND A.WeightFactor = B.MaxWeight;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244