Drop matching rows in a query

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.  
WonderwallAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
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
 
rockiroadsCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
mcv22Commented:
DELETE t OUTPUT DELETED.* FROM table t WHERE ID IN (@ID1, @ID2)
0
 
cyberkiwiCommented:
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
 
rockiroadsCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.