Re: Extracting Latest Date

On Thu, 04 Aug 2005 07:31:53 GMT, Jan S via SQLMonster.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)
.

Relevant Pages

  • Re: How to do an Access Database Query in Excel
    ... This is the Macro I've recorded to do exactly what I manually do to extract ... Before it exports to Excel I wish it to SUM by Location ... each Item sales, so not sure where I would put that in the code below. ...
    (microsoft.public.excel.programming)
  • If Function - Date Problem
    ... particular customer's sales but as individual lines, ... Ultimately I need to extract this info monthly to perform some ... original sales sheet. ... I've looked at pivot tables but they just seem to provide totals. ...
    (microsoft.public.excel.worksheet.functions)
  • Query Records since last run
    ... I run the same query, I want to extract only records since the last ... Customer Sales Date Sales Value ... I only want to extract records added to the sales table SINCE ...
    (microsoft.public.access.queries)
  • Re: How to sum the table records?
    ... I using the "list" function to extract the sales text delimited file ... I'm sure it's possible in one query, but I'm not familiar enough with JOINS ... That's more of a database question, ...
    (comp.lang.php)
  • Re: GW sales down
    ... hobby stores have thrived over the last 20 years or so on role play games, ... Role play games and movie merchandise are in decline; ... be bought in mass market outlets which hurts hobby store sales. ... Support promises by sales department that never materialize including: ...
    (rec.games.miniatures.warhammer)