By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with cte as (
select '1' as id, '1' as val
union all
select '2' as id, '1' as val
union all
select '2' as id, '2' as val
union all
select '3' as id, '1' as val
union all
select '3' as id, '1' as val
union all
select '3' as id, '1' as val
union all
select '3' as id, '2' as val
union all
select '3' as id, '2' as val
),
a as(
select id,val,count(id) as cou
from cte
group by id,val
)
select * from (
select *,max(cou) over(partition by id) as mcou from a
) b
where cou = mcou
| id | val | cou | mcou |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 1 | 3 | 3 |