Re: Mental block

Hi Charlie,

Keep it simple :

delete from cust a
where exists (select 1 from cust b
where a.cus_id = b.cus_id

   and a.start_date < b.start_date);

A normal b-tree index on cus_id => no sweat.

• Kenneth Koenraadt

On 13 Oct 2003 04:00:11 -0700, charlie3101_at_hotmail.com (Charlie Edwards) wrote:

>Hi,
>
>I've got a mental block over this one. I'd be grateful if somebody
>could help.
>I've got a table that links customers with addresses along with the
>data at which the record became effective. What I need is to get rid
>of all but the most recent record for each customer. Here's an
>example of what I mean ...
>
>
>CUS_ID ADDR_ID START_DATE
>-------- -------- -----------
> 288 453 02-AUG-2003 <- Delete this row
> 288 911 09-AUG-2003
> 468 263 02-AUG-2003
> 577 453 02-AUG-2003
> 989 638 03-JAN-2003 <- Delete this row
> 989 744 05-JUN-2003 <- Delete this row
> 989 823 22-SEP-2003
>
>So I'd end up with one record for each customer like this
>
>CUS_ID ADDR_ID START_DATE
>-------- -------- -----------
> 288 911 09-AUG-2003
> 468 263 02-AUG-2003
> 577 453 02-AUG-2003
> 989 823 22-SEP-2003
>
>How can I do this in SQL? We're talking about 20 million plus rows
>here, so I'm looking for the most efficient way of doing this.
>
>TIA
>
>CE
Received on Mon Oct 13 2003 - 13:12:01 CDT