Re: Help with Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/24/04 

Date: Fri, 24 Dec 2004 23:55:00 +0100

On Fri, 24 Dec 2004 16:43:57 -0600, Jeff Lynch wrote:

>I have a table containing sales order line item notes and want to return
>only the "latest" note for each sales order's line item. A sample table is
>shown below.
>
>Order Line NoteNum NoteDate Note
>1234 001 010 12/01/2004 This is the first note for this item
>1234 001 020 12/02/2004 This is the second note for this item
>5678 001 010 11/15/2004 This is the first note for this other
>item
>
>Desired Results:
>
>Order Line NoteNum NoteDate Note
>1234 001 020 12/02/2004 This is the second note for this item
>5678 001 010 11/15/2004 This is the first note for this other
>item
>
>Any help is greatly appreciated

Hi Jeff,

What exactly is the "latest" not? The one with the most recent NoteDate or
the one with the highest NoteNum?

Try something like this:

SELECT Order, Line, NoteNum, NoteDate, Note
FROM Notes AS n
WHERE NOT EXISTS
 (SELECT *
  FROM Notes AS n2
  WHERE n2.Order = n.Order
  AND n2.NoteDate > n.NoteDate)

Or something like this:

SELECT n.Order, n.Line, n.NoteNum, n.NoteDate, n.Note
FROM Notes AS n
INNER JOIN (SELECT Order, MAX(NoteDate)
            FROM Notes
            GROUP BY Order) AS n2(Order, MaxDate)
      ON n2.Order = n.Order
      AND n2.MaxDate = n.MaxDate

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)