Re: Mental block

Oops, should be just

  PARTITION BY CUS_ID (Sorry)

• Tom

In article <MPG.19f4b70017f044499896b6_at_news.t-online.de>, Tojo_at_hotmail.com says...
> 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.
Received on Mon Oct 13 2003 - 07:29:29 CDT