CODEWITHSUNDEEP
sql
user3324536
user3324536

Reputation: 335

SQL - Remove duplicates based on certain columns and not others

I don't really know how to word this problem clearly so perhaps I'll use an example:

Column1    Column2   Column3
A          100       239.1
A          100       0
A          101       191.3
B          99        0
B          99        12.43

I want to be able to see which have duplicates pertaining only to column1 and column2, and then compare those duplicates based on the values they have in column3, and remove the one which has a 0.

So my resulting table would look like:

    Column1    Column2   Column3
    A          100       239.1
    A          101       191.3
    B          99        12.43

I'm not entirely sure how to do this though, any help would be appreciated.

Upvotes: 3

Views: 5429

Answers (4)

Hernán Daniel Garau
Hernán Daniel Garau

Reputation: 136

for view rows should work

select col1,col2,col3 FROM yourtable t WHERE col3>0 OR (col1,col2) NOT IN (SELECT col1,col2 FROM yourtable t2 WHERE col3>0 AND t.col1 = t2.col1 and t.col2 = t2.col2)

if do you like delete those rows try

delete from yourtable t WHERE col3=0 AND (col1,col2) = ANY (SELECT col1,col2 FROM yourtable t2 WHERE col3>0 AND t.col1 = t2.col1 and t.col2 = t2.col2)

Upvotes: 0

Seda
Seda

Reputation: 243

You might be looking for a GROUP BY clause

Select column1, column2, SUM(column3) as col3 GROUP BY column2;

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

Based on your data you could simply DELETE FROM tab WHERE column3 = 0, but this might be too simplified.

This checks for 0 and additional rows <> 0 in column3:

select *
from tab as t1
where column3 = 0
and exists
 ( 
   select *
   from tab as t2
   where t1.column1 = t2.column1
     and t1.column2 = t2.column2
     and t2.column3 <> 0
 )

Simply change to DELETE if this returns the correct rows...

Upvotes: 0

Stuart
Stuart

Reputation: 6785

You need to join the table to itself, something like (if table is called "figures" for eg) 

select yourcols  
    from figures a, figures b  
    where a.col1 = b.col1  
    and a.col2 = b.col2  
    and (a.col3 = '0' or b.col3 = '0')

and the change that to a delete when you're happy it finds your rows to delete...

Upvotes: 0

Related Questions