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
CodePudding user response:
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)
CodePudding user response:
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