| Hugo Kornelis 2005-08-04, 8:24 pm |
| On Thu, 04 Aug 2005 07:31:53 GMT, Jan S via webservertalk.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)
|