Exclude duplicates by a certain field

user_16037525 0
Viewed 45

I have the following situation:

   id     ||   attribute_1 | attribute_2 | attribute_3
    1     ||       3             2            null
    2     ||       2             2            null
    3     ||       1             4            null
    4     ||       3             2             44
    5     ||       2             2             55

Rows 1 and 4, 2 and 5 are almost identical save upon value of attribute number 3.

How can I get all rows that have duplicates by values in attribute_1 and attribute_2 but attribute_3 is null?

The result should be next:

     id  ||   attribute_1 | attribute_2 | attribute_3
      1  ||       3             2            null
      2  ||       2             2            null
postgresql sql
2 Answers

You can try use COUNT window function.

SELECT *
FROM (
    SELECT *,COUNT(*) OVER(PARTITION BY attribute_1 ,attribute_2) cnt
    FROM T 
) t1
WHERE cnt > 1 AND attribute_3 IS NULL

sqlfiddle

user_51760710

One method is using exists

select *
from tbl t
where attribute_3 is null 
    and exists ( select 1 
                 from tbl t2
                 where t1.attribute_1 = t2.attribute_1
                   and t1.attribute_2 = t2.attribute_2
                   and t2.attribute_3 is not null)
user_62199790
Related