Dynamic delete 1

[Laeg]

Given tblA
Xid int, id int, create_date datetime, foo varchar(100)

Xid id create_date foo
1268 13129 29/10/2008 16:00 62459710
1269 13130 29/10/2008 16:00 62459710
1270 13131 29/10/2008 16:00 62459710
1271 13132 29/10/2008 16:00 62459710
1272 13133 29/10/2008 16:00 62459710
1273 13129 29/10/2008 16:00 62459710
1274 13130 29/10/2008 16:00 62459710
1275 13131 29/10/2008 16:00 62459710
1276 13132 29/10/2008 16:00 62459710
1277 13133 29/10/2008 16:00 62459710

There are multiple entries for foo value. How do you say keep the block of ids that begin with the higher Xid. So in this example foo 62459710 has id block (13129 - 13133) which is repeating. How do I delete the first block Xid (1268 - 1272) but keep that highlighted (ie the block with the greatest Xid values) in bold above?

Thanks for replies

 

[PHV]

DELETE FROM tblA
WHERE Xid NOT IN (SELECT MAX(Xid) FROM tblA GROUP BY foo,id)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Laeg]

Sorry to follow up but the data I'm working on is particularly poor. In my original example the repeating foo value associated with the id blocks was always the same. However it also seems that the foo value associated with a repeating block of ids can be different as you can see so the repeating id is really the key. So given the table below how is it possible to say for the batch of ids (13129-13133) irrespective of foo value, choose the higher block of Xids?

Xid id create_date foo
1268 13129 29/10/2008 16:00 12763989
1269 13130 29/10/2008 16:00 12763989
1270 13131 29/10/2008 16:00 12763989
1271 13132 29/10/2008 16:00 12763989
1272 13133 29/10/2008 16:00 12763989
1273 13129 29/10/2008 16:00 62459710
1274 13130 29/10/2008 16:00 62459710
1275 13131 29/10/2008 16:00 62459710
1276 13132 29/10/2008 16:00 62459710
1277 13133 29/10/2008 16:00 62459710

Thanks for replies.

 

[PHV]

So, even simpler:

DELETE FROM tblA
WHERE Xid NOT IN (SELECT MAX(Xid) FROM tblA GROUP BY id)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Laeg]

Thanks, * hangs head in shame *