Re: Complex query - PLEASE HELP!

S. wrote on 02.02.2008 08:30:
> 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.

Hmm. What about:

SELECT *
FROM cust_table ct1
WHERE orderdate = (
      SELECT max(orderdate)
      FROM cust_table ct2
      WHERE ct1.customer = ct2.customer
      AND orderdate < '2007-12-20'
      )