Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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.  
0
Wonderwall
Asked:
Wonderwall
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
 
cyberkiwiCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now