Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Mental block

Re: Mental block

From: <Kenneth>
Date: Mon, 13 Oct 2003 18:12:01 GMT
Message-ID: <3f8ae9b3.4111782@news.inet.tele.dk>


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.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US