Gabor Kalman Gabor Kalman - 1 month ago 4
SQL Question

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:


  • 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!

Answer

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.

Comments