Mental block

From: Charlie Edwards <charlie3101_at_hotmail.com>
Date: 13 Oct 2003 04:00:11 -0700
Message-ID: <217ac5a8.0310130300.46b81c8d@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.

TIA CE Received on Mon Oct 13 2003 - 06:00:11 CDT