Re: Alternative to a correlated query?

> Hi
>
> I am currently working on a database app which makes use of a
> co-related query.
>
> Consider the following example:
>
> Suppose I have a table with
>
> 1. customer ID
> 2. customer name
> 3. customer order date
>
> now, it is possible that for the same customer, (same customer ID),
> there are more than one order dates as she might have purchased more
> than once. i need to only latest order for each customer.
>
> so i currently have a query somewhat like this:
>
> select
> customer ID,
> customer name,
> order date
> from
> customer_table "main"
> where
> order date = (select max(order date)
> from
> customer_table "sub"
> where
> sub.order date = main.order date))
>
> this works fine, but the performance is VERY bad (and given the
> correlation inherent in the query, it is understandable).
>
> what would be an alternative to it. i bet this is a very commonly
> encountered scenario..
>
> (i tried using a subquery where i sort the dates in a descending order
> and get the first row for each customer (rownum = 1) but it seems the
> row ordering is done prior to the ORDER by clause so it doesnt help!

Right!

>
>
> :(
>
> would appreciate any help/advice/comments,
>
> thanks a bunch in advance!
>
> shanx
>

I don't think that your given statement works right.

I would do it like this:

select

    customer_ID,
    customer_name,
    max(order_date)
from customer_table
group by

    customer_ID,
    customer_name

Is this what you want? Let me know.

Regards, Stephan
--

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------