Consult the most duplicated record

Question

I have a problem to recover the teacher who teaches more subjects in this table, in which case I want to recover the code that has more duplicated records in the cod_prof column.

create table aluno_professor_disciplina (
    matricula int,
    cod_prof int,
    cod_disc int,
    primary key (matricula, cod_prof, cod_disc),
    foreign key (matricula) references aluno(matricula),
    foreign key (cod_prof) references professor(cod_prof),
    foreign key (cod_disc) references disciplina(cod_disc));

INSERT INTO aluno_professor_disciplina VALUES (1,1,1);
INSERT INTO aluno_professor_disciplina VALUES (1,5,2);
INSERT INTO aluno_professor_disciplina VALUES (2,2,3);
INSERT INTO aluno_professor_disciplina VALUES (3,3,5);
INSERT INTO aluno_professor_disciplina VALUES (3,4,5);
INSERT INTO aluno_professor_disciplina VALUES (1,5,5);

Someone could help me?

I’m tempted to, but it’s not working:

select A.nome, count(B.cod_prof) as QT_DISC from professor as A 
inner join aluno_professor_disciplina as B 
    on A.cod_prof = B.cod_prof
group by (A.nome);

He returns the name of the teachers and the amount of disciplines that each one teaches, wanted to know how I can recover only what teaches more disciplines.

Answer 1

If your query already returns the correct data of the amount it teaches you can sort by decreasing QT_DISC (from largest to smallest) and then select (limit) only 1 record.

select A.nome, count(B.cod_prof) as QT_DISC
from professor as A 
inner join aluno_professor_disciplina as B 
    on A.cod_prof = B.cod_prof
group by A.nome
order by QT_DISC DESC
LIMIT 1;