Products with Last PurchaseID

[mikewin86]

Hello ,

I would like to ask a question concerned with SQL Server 2000 query.
I've got a table PurchaseDetails and I would like to query from it.
I want all product records with last purchase ID. The following is my query statements.
SELECT MAX(PurchaseID) AS LastID, ProductID, BatchNo
FROM PurchaseDetails
GROUP BY ProductID, BatchNo

When I run this query statements , I got all products and all records (i.e. not only last PurchaseID but also old PurchaseID). So please help me how to query as I want.

[yarbrough40]

...I want all product records with last purchase ID...

I don't understand this. Do you mean you want all products where the PurchaseID field has a value in it? please explain with more detail.

[mikewin86]

Purchase Deatails Table includes the following fields.

PurchaseDetails ID
PurchaseID
ProductID
BatchNo
Quantity
Price
Amount

here sample data



200 100 20 100 A10000
201 101 20 120 B10001
202 102 20 50 D01111

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

102 20 50 D01111

[yarbrough40]

Assuming that your PurchasID is a numeric field

Enjoy!

SELECT TOP 1 * From PurchaseDetails ORDER BY PurchaseID DESC

[mikewin86]

Thanks yarbrough. But it return only one row and include one product.

[yarbrough40]

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

102 20 50 D01111

all you asked for was a single record (see your quote above). Please explain better what you are trying to do. I can't read your mind my friend.

[mikewin86]

I am sorry friend. I need in such a hurry. So I forgot to tell u what I really want. In fact I need all products(i.e. all products with last PurchaseID) from table. The one I show you is an example for product ID 20 and I have got about 100 product IDs.
Thanks for your patience.

[yarbrough40]

so let me help to clarify.. tell me if I am correct ok? You have this table "PurchaseDetail s" and in this table is a column called "PurchaseID ". This PurchaseID field is numeric. There are multiple records that have the same PurchaseID number. What you would like is to return all records in this table with the largest PurchaseID number (don't say "last"... say "largest" because I have no idea what you mean by "last").

is this what you want?



-or is this more accurate.......

Every PurchaseID number is unique and they incriment (get larger) as more records are added. What you want is to find that largest PurchaseID number, then find what ProductID that it is associated with, then return all records with that same ProductID.

???????

[mikewin86]

Yes friend. PurchaseID is numeric field and there are multiple records with same PurchaseID. I would like to get largest PurchaseID for each ProductID.

[yarbrough40]

SELECT Max(PurchaseID) AS LargestPurchaseID, ProductID
FROM PurchaseDetails
GROUP BY ProductID;