Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dynamic delete 1

Status
Not open for further replies.
Laeg

Laeg

Programmer
Nov 29, 2004
95
IE
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

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
So, even simpler:
Code: 
DELETE FROM tblA
WHERE Xid NOT IN (SELECT MAX(Xid) FROM tblA GROUP BY id)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks, * hangs head in shame *
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jasonhuibers
  • Locked
  • Question
Delete Attachments for Table
Replies
2
Views
260
jasonhuibers
jasonhuibers
nkamp
  • Locked
  • Question
Delete child records before I can delete the parent
Replies
2
Views
236
Thargy
Thargy
lllyxer
  • Locked
  • Question
Dynamic WHERE clause alternative.
Replies
1
Views
99
rac2
rac2
micheals
  • Locked
  • Question
Dynamic Column
Replies
2
Views
128
carp
carp
ThaDoctor
  • Locked
  • Question
Do I need a cursor or dynamic SELECT?
Replies
1
Views
122
PHV
PHV

Part and Inventory Search

Sponsor

Back
Top