by Lennart <Erik.Lennart.Jonsson@[EMAIL PROTECTED]
>
Feb 2, 2008 at 01:30 AM
On Feb 2, 8:30 am, "S." <sianeag...@[EMAIL PROTECTED]
> wrote:
> Hi guys,
>
> Any ideas with an SQL that will retrieve the expected results?
>
> Expected Results:
> ==============
> ID orderdate customer
> 2 20/02/2007 JONES
> 4 20/06/2007 SMITH
> 6 20/05/2007 BROWN
>
> The above results are from the following resultset in the database:
> ================================================
> ID orderdate customer
> 1 20/01/2007 SMITH
> 2 20/02/2007 JONES
> 3 20/03/2007 BROWN
> 4 20/06/2007 SMITH
> 5 20/12/2007 SMITH
> 6 20/05/2007 BROWN
>
> I need the:
> - ID of the record
> - the customer name
> - most recent orderdate that occurs for that customer
> - only the records where the orderdate is less than 20/12/2007
>
> I have been working on this for a while and I have tried the MAX
> function and nested SELECT queries all ending with just frustration.
>
> Hope you can help!
> S.
What is the maximum orderdate per customer before 20/12/2007? Given
that, can you determine the id for each such row?
/Lennart
