Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table some_table (parent varchar (50), child varchar (50))

CREATE TABLE


insert into some_table values ('parent1', 'child1'),
('parent1', 'child1'),
('parent1', 'child12'),
('parent1', 'child13'),
('parent1', 'child14'),
('parent1', 'child2'),
('parent1', 'child2'),
('parent1', 'child2'),
('parent1', 'child2'),
('parent2', 'child1'),
('parent2', 'child12'),
('parent2', 'child1'),
('parent3', 'child4')

INSERT 0 13


select distinct s1.parent, s3.child
from some_table s1 
INNER JOIN LATERAl (select s2.parent, s2.child, count (s2.child) cnt
from some_table s2
where s2.parent=s1.parent
group by s2.parent, s2.child
order by cnt desc
limit 1) s3 on s1.parent=s3.parent


parent
child


parent1
child2

parent2
child1

parent3
child4



SELECT 3