Complex query - PLEASE HELP!

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." 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

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'
)

Thanks Thomas,

That was exactly what i was after!

S.

On Feb 3, 1:39 am, "S." wrote:
> Thanks Thomas,
>
> That was exactly what i was after!
>
> S.

5 stars! For that! Too generous! ;-)

I think there must be a solution that doesn't require a subquery but
my brains a bit frazzled today. I think it could be something along
these lines, but I'd have to extend the dataset to be sure:

SELECT t1.*
FROM orders t1
LEFT JOIN orders t2
ON t1.customer = t2.customer
AND t1.orderdate < t2.orderdate
AND t2.orderdate < '2007-12-20'
WHERE t2.id IS NULL
HAVING orderdate < '2007-12-20';

strawberry wrote on 05.02.2008 23:34:
> On Feb 3, 1:39 am, "S." wrote:
>> Thanks Thomas,
>>
>> That was exactly what i was after!
>>
>> S.
>
> 5 stars! For that! Too generous! ;-)
>
> I think there must be a solution that doesn't require a subquery but
> my brains a bit frazzled today. I think it could be something along
> these lines, but I'd have to extend the dataset to be sure:
>
> SELECT t1.*
> FROM orders t1
> LEFT JOIN orders t2
> ON t1.customer = t2.customer
> AND t1.orderdate < t2.orderdate
> AND t2.orderdate < '2007-12-20'
> WHERE t2.id IS NULL
> HAVING orderdate < '2007-12-20';

A subquery is not necessarily slower or less efficient than a join. But that
depends on the DBMS and the indexes available for the involved tables.

Tomas