How to remove duplicate results based on an aggregate field?-CodePudding

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

CodePudding user response：

Using GROUP BY :

SELECT
    max(*)
FROM
    (
        select concat(fieldA,'_',fieldB) as new_field,
        field c,
        field d
        from tableA
        where field ='false' and fieldy='true'
        group by 2,3
    )
GROUP BY
    new_field
;

CodePudding user response：

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!