472,102 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,102 software developers and data experts.

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 1159
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.


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
320 100+
Assuming that your PurchasID is a numeric field

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
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
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
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
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.