Extracting Latest Date

I have a db which stores all my sales transactions.
The task is to extract the buy price(price) for each item(ItemCode) where the
date is the latest for each item.
For Example:
Sales Table
ItemCode Qty Price Date
-- -- -- --
XYZ 2 23.50 12/03/05
XYZ 3 23.50 13/03/05
XYZ 2 99.99 14/03/05
ABC 14 88.88 01/01/05
RDB 12 77.30 21/04/05
RDB 23 23.35 02/05/05
I would then require only
ItemCode Qty Price Date
-- -- -- --
XYZ 2 99.99 14/03/05
ABC 14 88.88 01/01/05
RDB 23 23.35 02/05/05
How do i write a query to extract the data?
Need help with a project of mine...really stuck with this one..Thank you
On Thu, 04 Aug 2005 07:31:53 GMT, Jan S via droptable.com wrote:

>I have a db which stores all my sales transactions.
>The task is to extract the buy price(price) for each item(ItemCode) where the
>date is the latest for each item.
(snip)
>How do i write a query to extract the data?
> Need help with a project of mine...really stuck with this one..Thank you
Hi Jan,
Method #1:
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
WHERE NOT EXISTS
(SELECT *
FROM Sales AS b
WHERE b.ItemCode = a.ItemCode
AND b.[Date] > a.[Date])
Method #2:
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
WHERE a.[Date] =
(SELECT MAX(b.[Date])
FROM Sales AS b
WHERE b.ItemCode = a.ItemCode)
Method #3:
SELECT a.ItemCode, a.Qty, a.Price, a.[Date]
FROM Sales AS a
INNER JOIN (SELECT ItemCode, MAX([Date]) AS MaxDate
FROM Sales
GROUP BY ItemCode) AS b
ON b.ItemCode = a.ItemCode
AND b.MaxDate = a.[Date]
(And there might even be more methods...)
If performance is important, then test each of these queries a few times
and use the one that's the fastest. Otherwise, use the one that you find
the easiest to understand, as you'll have to maintain it later.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo..Much appreciated
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...-mseq/200508/1