Product with Last Purchase Date

Question

Hello ,

I would like to query from SQL Server 2000 Database.I have got two tables. They are Purchase and PurchaseDetails. I would like to get product records with Last Purchase ID but I can't query with the following statements.So please help me.

SELECT     TOP 100 PERCENT dbo.Purchase.PurchaseID AS LastOfPurchaseID,  dbo.PurchaseDetails.ProductID, 
                      MAX(dbo.Purchase.PurchaseDate) AS LastOfPurchaseDate
FROM         dbo.Purchase INNER JOIN
                      dbo.PurchaseDetails ON dbo.Purchase.PurchaseID = dbo.PurchaseDetails.PurchaseID 
GROUP BY dbo.PurchaseDetails.ProductID, dbo.Purchase.PurchaseDate,dbo.Purchase.PurchaseID
ORDER BY MAX(dbo.Purchase.PurchaseDate) DESC

Answer 1

Couldn't you just order by purchase ID in DESC ORDER? Everything gets a little weird when you try to do things with MSSQL ...

Answer 2

If you are trying to retrieve the list of products based on the most recently entered order's date, then use a sub-query:

SELECT  Purchase.PurchaseID, Purchase.PurchaseDate, PurchaseDetails.ProductID
FROM      Purchase 
    INNER JOIN PurchaseDetails 
    ON Purchase.PurchaseID = PurchaseDetails.PurchaseID
WHERE   (Purchase.PurchaseDate = (SELECT MAX(PurchaseDate) FROM Purchase))