Remove duplicates based on two columns

Question

I have this table, and i would like a SELECT to exclude the lines marked. The general rule would be:

IF there are two or more lines where the controlname AND brandname AND grouptypes columns are equal
THEN keep the row where groupname is NOT 'Keine Zuordnung'.

CONTROLNAME BRANDNAME GROUPTYPES GROUPNAME ECU AUDI VERNETZER 1 ECU AUDI VERNETZER Keine zuordnung <--THIS ECU AUDI FUSI Keine zuordnung <--THIS ECU AUDI FUSI 2 ECU2 AUDI FACHANWENDER Keine zuordnung ECU3 AUDI FACHANWENDER Keine zuordnung

Can i have a little help with this please? Thank you!

Do you want avoid to SELECT the "duplicate", or do you want to DELETE the "duplicate" from the table? — jarlh
– jarlh, Commented Oct 20, 2016 at 14:56
What should be the result (regardless of whether you need "DELETE" or just "SELECT but exclude.") - so, what should be the result if there are duplicate rows like you explained, but ALL the duplicates have 'Keine zuordnung' as GROUPNAME? Delete (or exclude) all of them? Or keep exactly one copy and delete (or exclude) the other(s)? — user5683823
– user5683823, Commented Oct 20, 2016 at 18:26
@mathguy All duplicate rows have 'Keine Zuordnung' as GROUPNAME. I need to select only the ones that are duplicate AND their GROUPNAME is NOT 'Keine Zuordnung' + the ones that are not duplicates. — KGBR
– KGBR, Commented Oct 21, 2016 at 4:43

Gordon Linoff · Accepted Answer · 2016-10-20 14:45:37Z

Here is one method:

select t.* from (select t.*, count(*) over (partition by controlname, brandname, grouptypes) as cnt from t ) t where cnt = 1 or groupname <> 'Keine Zuordnung';

It uses a window function to get the count and then a where for your logic.

Collectives™ on Stack Overflow

Remove duplicates based on two columns

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related