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.

On Feb 2, 8:30 am, "S." <sianeag... (AT) gmail (DOT) com> wrote:
What is the maximum orderdate per customer before 20/12/2007? Given
that, can you determine the id for each such row?

/Lennart

On Feb 2, 8:30 am, "S." <sianeag... (AT) gmail (DOT) com> wrote:
What is the maximum orderdate per customer before 20/12/2007? Given
that, can you determine the id for each such row?

/Lennart

On Feb 2, 8:30 am, "S." <sianeag... (AT) gmail (DOT) com> wrote:
What is the maximum orderdate per customer before 20/12/2007? Given
that, can you determine the id for each such row?

/Lennart

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

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

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

Thanks Thomas,

That was exactly what i was after!

S.

Thanks Thomas,

That was exactly what i was after!

S.

Thanks Thomas,

That was exactly what i was after!

S.