Products with Last Purchase Date and Last Purchase ID

Question

Products with Last Purchase Date and Last Purchase ID

ceomike

SSC Veteran

Points: 238
More actions
March 15, 2010 at 4:00 am

#217793

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ceomike SSC Veteran Points: 238 More actions · Answer 2

Purchase Table includes the following fields.

PurchaseID

PurchaseDate

Remark

Purchase Deatails Table includes the following fields.

PurchaseDetailsID

PurchaseID

ProductID

BatchNo

Quantity

Price

Amount

here sample data

PurchaseID PurchaseDate

100 12/1/2009

101 12/20/2009

102 12/30/2009

PurchaseDetailsID PurchaseID ProductID Quantity BatchNo

200 100 20 100 A10000

201 101 20 120 B10001

202 102 20 50 D01111

I would like to get product with Last Purchase Date and Last Purchase ID as below.

PurchaseID Date Product Qty BatchNo

102 12/30/2009 20 50 D01111

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm going to assume you didn't read the article.... Please do so.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass