SQL Query - Most recent date in a group - Select another field from the group

[videoclocknet]

Hi guys!


I'm trying to make a so simple SQL Query and I'm embarrassed I Can't get it working. I've been looking around the web and found similar answers, but not exactly what I'm looking for.


We've got 3 different tables:
- CUSTOMERS table:
Fields: CustomerID (PK), CustomerName,
- PRODUCTS table:
Fields: ProductID (PK), ProductName
- PRIZES table:
Fields: CustomerID (FK), ProductID (FK), Date, Prize


Just to say that in Prizes table, a customer can have more than one prize for the same product at different dates. So for a given customer and a given product, only the most recent prize is the correct one.


That said, I want to make a query that shows: for each customer its name and all the related products and its code->for each product only the most recent prize and the date that prize was stated

SELECT C.CustomerName, P.ProductID, P.ProductName, MAX(Pr.date)
FROM Customers C, Products P, Prizes Pr
WHERE C.CustomerID=Pr.CustomerID AND P.ProductID=Pr.ProductID
GROUP BY C.CustomerName, P.ProductID, P.ProductName;

But with such a query I can't show the prize.


I've also tried with:

SELECT C.CustomerName, P.ProductID, P.ProductName, Pr.date, Pr.Prize
FROM Customers C, Products P, Prizes Pr
WHERE C.CustomerID=Pr.CustomerID AND P.ProductID=Pr.ProductID AND Pr.date =
    ( SELECT MAX(PRI.date)
      FROM pRIZES PRI
      WHERE PRI.CustomerID=Pr.CustomerID AND PRI.ProductID=PR.ProductID
    );

But it shows me an error as well.
Could you give a hand?



Kind Regards

[orange]

What is the error message/number?

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

[videoclocknet]

Thanks guys for your support. Following the link you have provided to me, it seems that the query I'm looking for is as following:

SELECT C.CustomerName, P.ProductID, P.ProductName, Pr.date, Pr.Prize
FROM Customers C, Products P, Prizes Pr
WHERE C.CustomerID=Pr.CustomerID AND P.ProductID=Pr.ProductID AND Pr.date IN
   (SELECT TOP 1 PRI.date                            
   FROM Prizes AS PRI                             
   WHERE PRI.ProductID=PR.productID AND PRI.CustomerID=PR.CustomerID       
   ORDER BY PRI.date DESC) 
ORDER BY C.CustomerName, P.ProductID, P.ProductName, Pr.date, Pr.Prize;

Is it right?

[June7]

Did you test it?

[videoclocknet]

I will test it tomorrow when I arrive to my workplace and i'll let you know. I haven't got access to the DB from outside it.

Thanks

[videoclocknet]

Ey guys, the following query worked perfectly.

SELECT C.CustomerName, P.ProductID, P.ProductName, Pr.date, Pr.Prize
FROM Customers C, Products P, Prizes Pr
WHERE C.CustomerID=Pr.CustomerID AND P.ProductID=Pr.ProductID AND Pr.date IN
   (SELECT TOP 1 PRI.date                            
   FROM Prizes AS PRI                             
   WHERE PRI.ProductID=PR.productID AND PRI.CustomerID=PR.CustomerID       
   ORDER BY PRI.date DESC)

Thanks for your support.