Click here to Skip to main content
14,975,411 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Dear,

I have table called Trn_Purchase
fields are
ItemCode
ItemDesc
PurchaseDates
Price

I want to get the last purchase price depend on date

table have e.g below data

ItemCode PurchaseDate Price
A 01-Mar-2010 4
A 02-Mar-2011 6
then result should


itemCode PurchaseDate Price
A 02-Mar-2011 6

How to do this

Thanks
Basit.

What I have tried:

I did below
SQL
   SELECT
 p.*
FROM
 Trn_Purchase p,
 (SELECT * FROM Trn_Purchase) m
WHERE 
 p.Itemcode = m.Itemcode
 and p.[Purchase Date] = m.[Purchase Date]
Posted
Updated 15-Apr-16 19:20pm
v4
Comments
Richard Deeming 15-Apr-16 13:42pm
   
You've tagged the question as both "Access" and "SQL Server 2008 R2". Which is it?
Philippe Mori 15-Apr-16 16:44pm
   
Learn to format your question.
Philippe Mori 15-Apr-16 16:52pm
   
I don't think it is possible to do efficient request for that. I think that usually one would use distinct table for actual data that for historical data otherwise it would penalize most requests even though you would rarely use old data.
Philippe Mori 15-Apr-16 16:59pm
   
By the way, your question is not clear. If you have multiple item code, then your sample data should show multiple codes too and it should show the desired result in that case.

It make no sense to provide simplistic data if you want complex query. At least, you should provide data that could prove if a query always give the desired result.

1 solution

Try:
SQL
SELECT TOP 1 * FROM Trn_Purchase ORDER BY PurchaseDate DESC


"yes each item code"
If you want a specific output, then give specific examples, and explain exactly what you want: it means we can reply with a specific solution!

Try:
SQL
SELECT ItemCode, PurchaseDate, Price 
FROM (SELECT  *, ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER By PurchaseDate DESC) AS rn
      FROM Trn_Purchase) x
WHERE rn = 1
   
v2
Comments
Philippe Mori 15-Apr-16 16:53pm
   
I would think that OP would want the latest price for each item code...
basitsar 16-Apr-16 4:02am
   
yes each item code
OriginalGriff 16-Apr-16 4:52am
   
Answer updated
basitsar 16-Apr-16 6:18am
   
thanks alot.
is this work in msaccess.

SELECT ItemCode, PurchaseDate, Price
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ItemCode ORDER By PurchaseDate DESC) AS rn
FROM Trn_Purchase) x
WHERE rn = 1
OriginalGriff 16-Apr-16 6:20am
   
You're welcome!
basitsar 16-Apr-16 8:30am
   
Dear im asking in msaccess this query also work?
basitsar 19-Apr-16 5:39am
   
Is there any update for msaccess

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900