LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Scripts, Code Samples and SSMS Custom Reports » All SQL Server Versions » how to get Products with Last Purchase Date and Last Purchase ID

how to get Products with Last Purchase Date and Last Purchase ID

Options

Previous Topic · Next Topic

mikewin86

Posted: Monday, March 15, 2010 5:01:52 AM

Rank: Newbie

Joined: 3/15/2010
Posts: 1
Points: 3
Where do you live?: NewYork

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 and Last Purchase Date 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

Scott Whigham

Posted: Monday, March 15, 2010 10:26:31 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

First, you never need a "TOP 100 PERCENT" query in a standalone query; the only time you need such is inside of a view/function when you want the results to be returned in a specific order.

Second, to return the "most recent" of anything requires a subquery:

...WHERE p.PurchaseDate = (
SELECT MAX(sub.PurchaseDate)
FROM dbo.Purchase sub
WHERE sub.ProductId = main.ProductId
)

I think that's probably it and, if not, should give you some ideas about what the approach should be. The trick with these types of queries is defining the parameters: what do you mean "last purchase date"? What if two of the same product were purchased at the same exact time - do you want to see both?

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.