Dynamic delete
Dynamic delete
(OP)
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
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
RE: Dynamic delete
WHERE Xid NOT IN (SELECT MAX(Xid) FROM tblA GROUP BY foo,id)
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Dynamic delete
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.
RE: Dynamic delete
CODE
WHERE Xid NOT IN (SELECT MAX(Xid) FROM tblA GROUP BY id)
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Dynamic delete