Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tab(col1 text, col2 text);

CREATE TABLE


insert into tab
VALUES
  ('A', 'B'),
  ('A', 'B'),
  ('A', 'B'),
  ('A', 'D'),
  ('A', 'D'),
  ('A', 'D'),
  ('C', 'F'),
  ('C', 'G'),
  ('C', 'F');

INSERT 0 9


-- Simpler & faster (and correct!)
SELECT DISTINCT ON (col1)
       col1, col2, count(*) AS conta
FROM   tab 
GROUP  BY col1, col2 
ORDER  BY col1, conta DESC;


col1
col2
conta


A
D
3

C
F
2



SELECT 2


with cte as (
  SELECT col1, col2, 
         count(*) as conta,
         dense_rank() over (partition by col1 order by count(*) desc) as rnk
  FROM tab 
  GROUP by col1, col2 
) 
select col1, col2, conta
from cte
where rnk = 1
order by col1, col2;


col1
col2
conta


A
B
3

A
D
3

C
F
2



SELECT 3


with cte as (
  SELECT col1, col2, 
         count(*) as conta,
         row_number() over (partition by col1 order by count(*) desc) as rnk
  FROM tab 
  GROUP by col1, col2 
) 
select col1, col2, conta
from cte
where rnk = 1
order by col1, col2;


col1
col2
conta


A
D
3

C
F
2



SELECT 2