Re: Mental block

In article <217ac5a8.0310130300.46b81c8d_at_posting.google.com>, charlie3101_at_hotmail.com says...
> 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
>

You didn't mention what version, but with 8i and up you can use analytical functions. E.g. this will assign row numbers to customer id's and their start dates in descending order:

SELECT ADDR_ID,CUS_ID,START_DATE,

       ROW_NUMBER() OVER
      (PARTITION BY CUS_ID,START_DATE
       ORDER BY CUS_ID,START_DATE DESC) AS row_num
      FROM MY_TABLE

I don't know if this is homework or not, so I'll let you do the DELETE part.

• Tom