0

I have created an aggregate of fieldA and fieldB (fieldA_field_B) as new_field. How to remove duplicate rows from the results based on that field alone? Using min() it still comes up with he same results.

select concat(fieldA,'_',fieldB) as new_field,
field c,
field d
from tableA
where field ='false' and fieldy='true'
group by 2,3
user4157124
  • 2,809
  • 13
  • 27
  • 42
Armine
  • 7
  • 4

1 Answers1

1

With the combination of CTE and Row_number(), we can remove the duplicate records from the table. This will find the duplicate records based on "fieldA" and "fieldB" (fieldA_field_B) and delete it from the table:


With CTE As
    (
        select *, row_number() over(partition by concat(fieldA,'_',fieldB) order by fieldA, fieldB) as RN
        from tableA
        where field ='false' and fieldy='true'
    )
delete from CTE where RN>1;

Please let me know if any information is needed on this. Thanks!

Data_Enginner_Suraj
  • 76
  • 4