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, Oct 20 '16 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)? — , Oct 20 '16 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, Oct 21 '16 at 04:43

score 4 · Accepted Answer · answered Oct 20 '16 at 14:45

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.

Remove duplicates based on two columns

1 Answers1

Linked