Can I isolate duplicate values a unique value field?

I have a situation where I have columns 1,2,3,4 where 1,2,3 can be duplicated values across the row and 4 can be unique. I am tryin to write a query that will give me duplicates of 1,2,3 and the difference of 4. So it would like,

1 2 3 4
a b c 5
a b c 7

I only want to return those grouped values so I can see how many I have where they are duplicated 1-3 but with a different 4. I have tried,

select 1,2,3,4
from tbl
where 1 IN (select 1 from tbl group by 1 having count(*) > 1)

The above isolates all variants - but I get extra values where 4 might be null. If I use 'where 4 is not null' I still retain non null variant that is count(*) > 1

I have also tried

select 1, 2, 3, 4
from
(
select 1,2,3,4
from tbl
where 1 IN (select 1 from tbl group by 1 having count(*) > 1))
group by 1,2,3

Above simply doesn't work because I need to also group 4 and then I just get the same results.

I know it's a simple tweak to something I just don't understand how to do yet. Can anyone help here, please? Thank you!

CodePudding user response：

where 4 is not null is the solution. You just have to do it in two places: in the subquery, to exclude those cases in the count, and in the main query, to remove these rows:

select 1,2,3,4
from tbl
where 1 IN (select 1 from tbl WHERE 4 IS NOT NULL group by 1 having count(*) > 1) 
AND 4 IS NOT NULL