469,150 Members | 1,955 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

Products with Last PurchaseID

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.
Mar 15 '10 #1
9 1100
yarbrough40
320 100+
...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.
Mar 15 '10 #2
Purchase Deatails Table includes the following fields.

PurchaseDetailsID
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
Mar 16 '10 #3
yarbrough40
320 100+
Assuming that your PurchasID is a numeric field

Enjoy!
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 * From PurchaseDetails ORDER BY PurchaseID DESC
Mar 16 '10 #4
Thanks yarbrough. But it return only one row and include one product.
Mar 16 '10 #5
yarbrough40
320 100+
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.
Mar 16 '10 #6
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.
Mar 16 '10 #7
yarbrough40
320 100+
so let me help to clarify.. tell me if I am correct ok? You have this table "PurchaseDetails" 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.

???????
Mar 16 '10 #8
Yes friend. PurchaseID is numeric field and there are multiple records with same PurchaseID. I would like to get largest PurchaseID for each ProductID.
Mar 17 '10 #9
yarbrough40
320 100+
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(PurchaseID) AS LargestPurchaseID, ProductID
  2. FROM PurchaseDetails
  3. GROUP BY ProductID; 
Mar 17 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by frizzle | last post: by
44 posts views Thread by Christoph Zwerschke | last post: by
24 posts views Thread by Rob R. Ainscough | last post: by
1 post views Thread by Bruce One | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.