Re: Alternative to a correlated query?

A copy of this was sent to shanx_at_shanx.com (Shashank Tripathi) (if that email address didn't require changing) On Tue, 26 Oct 1999 14:05:31 GMT, you wrote:

>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:
>

does the customer name vary by customr id? If not -- then

select customer_id, customer_name, max(order_date) from customer_table
group by customer_id, customer_name

would do it as well.

>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!
>
>:(
>
>would appreciate any help/advice/comments,
>
>thanks a bunch in advance!
>
>shanx
>
>
>Killing myself is the last thing I will ever do.
> -- Homer Simpson
>
>Shashank Tripathi
>http://shanx.com

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 26 1999 - 09:29:56 CDT