Query the most duplicate record

Question

Query the most duplicate record

Navigation

#1 by (1 votes)

2

I'm having a problem retrieving the teacher who teaches more subjects in this table, in that case I want to retrieve the code that most has double 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);

Could someone help me?

I'm tempted, 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);

It returns the names of the teachers and the amount of subjects each one teaches, wanted to know how I can recover only what teaches more subjects.

mysql sql

asked by anonymous 23.11.2015 / 19:01

1 answer

How can I make a link from my site open only when clicked on the same page? [closed] FTP connection is not listing files

score 1 · Accepted Answer

If your query already returns the correct data of the amount it teaches you can sort by QT_DISC decreasing (highest to lowest) and then selecting (limiting) 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;