Re: Mental block

"Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message news:217ac5a8.0310130300.46b81c8d_at_posting.google.com...
> 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.

Not tested for efficiency

SQL*Plus: Release 9.2.0.3.0 - Production on Mon Oct 13 12:56:09 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production

SQL> create table customer(
  2 cus_id number not null,
  3 addr_id number not null,
  4 start_date date);

Table created.

SQL> create index idx_custmer on customer(cus_id,start_date);

Index created.

SQL> insert into customer
values(288,453,TO_DATE('02-AUG-2003','DD-MON-YYYY'));

1 row created.

SQL> insert into customer
values(288,911,TO_DATE('09-AUG-2003','DD-MON-YYYY'));

1 row created.

SQL> insert into customer
values(468,263,TO_DATE('02-AUG-2003','DD-MON-YYYY'));

1 row created.

SQL> insert into customer
values(577,453,TO_DATE('02-AUG-2003','DD-MON-YYYY'));

1 row created.

SQL> insert into customer
values(989,638,TO_DATE('03-JAN-2003','DD-MON-YYYY'));

1 row created.

SQL> insert into customer
values(989,744,TO_DATE('05-JUN-2003','DD-MON-YYYY'));

1 row created.

SQL> insert into customer
values(989,823,TO_DATE('22-SEP-2003','DD-MON-YYYY'));

1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT * FROM CUSTOMER;     CUS_ID ADDR_ID START_DAT

---------- ---------- ---------
       288        453 02-AUG-03
       288        911 09-AUG-03
       468        263 02-AUG-03
       577        453 02-AUG-03
       989        638 03-JAN-03
       989        744 05-JUN-03
       989        823 22-SEP-03

7 rows selected.

SQL> DELETE FROM CUSTOMER WHERE (CUS_ID,START_DATE) IN(   2 SELECT CUS_ID,START_DATE
  3 FROM CUSTOMER
  4 MINUS
  5 SELECT CUS_ID,MAX(START_DATE)
  6 FROM CUSTOMER
  7* GROUP BY CUS_ID); 3 rows deleted.

SQL> SELECT * FROM CUSTOMER;     CUS_ID ADDR_ID START_DAT

---------- ---------- ---------
       288        911 09-AUG-03
       468        263 02-AUG-03
       577        453 02-AUG-03
       989        823 22-SEP-03

SQL> COMMIT; Commit complete.

SQL> Received on Mon Oct 13 2003 - 07:06:33 CDT