CODEWITHSUNDEEP
sql
Armine
Armine

Reputation: 7

How to remove duplicate results based on an aggregate field?

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

Upvotes: 0

Views: 220

Answers (1)

Data_Enginner_Suraj
Data_Enginner_Suraj

Reputation: 76

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!

Upvotes: 1

Related Questions