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!

:(

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 Received on Tue Oct 26 1999 - 09:05:31 CDT