CODEWITHSUNDEEP
sqloracle-databaseduplicates
KGBR
KGBR

Reputation: 505

Remove duplicates based on two columns

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

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!

Upvotes: 3

Views: 2676

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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.

Upvotes: 4

Related Questions