Solved

Drop matching rows in a query

Posted on 2010-08-23
7
327 Views
Last Modified: 2012-05-10
I have a query where I am matching 2 ID's. On a match I want to eliminate the row I am having a brain cramp here and can't remembr how to drop those rows. Thanks for the help.  
0
Comment
Question by:Wonderwall
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33506840
u want to return just unique rows? you could try adding distinct i.e.  select distinct ....
or do you want to show only records without duplicates?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33506847
How will you decide which one of the matches to keep OR are you wanting to delete both from the table? If you are just looking to select distinct, then you can try as above but since you said you are just matching on ID, I would suspect the other values of the row are unique and hence my question.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33506854
Delete tablea
where exists (
  select * from tableA B where tableA.ID=B.ID and B.uniquecolumn > tablea.uniquecolumn)

This gets rid of all extra records from tablea where the ID appears more than once.
Keeps the one with largest uniquecolumn.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 12

Expert Comment

by:mcv22
ID: 33506864
DELETE t OUTPUT DELETED.* FROM table t WHERE ID IN (@ID1, @ID2)
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33506876
If your select is

select * from tbl where ID in (123,345)

You can turn it into a DELETE

delete tbl where ID in (123,345)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33506887
if u wanting to remove the rows (I previously thought you meant dropping from display) then have a look at the ms approach http://support.microsoft.com/kb/139444
if its just for selecting, the other way is to use group by and having
select ...., count(*)
from ...
where ...
group by ...
having count(*)= 1
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33506900
I was thinking something like this for either:

;with t
as
(
   select *, row_number() over(partition by id order by some_column) rn
   from your_table_name
)
-- delete
select *
from t
where rn > 1;

This will show you the duplicates then you can comment out the "select *" line and uncomment the "delete" line.

Kevin
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now