How to show the record that was most repeated in the MYSQL table?

Question

How to show the record that was most repeated in the MYSQL table?

Navigation

#1 by (4 votes)

1

For example, if I did a record of John about 5 times (that is, I did 5 records of John) and then I registered other names normally, without repeating anything, I wanted to know how to give a select showing the name of John, since he was the one who repeated the most, does anyone know how?

mysql

asked by anonymous 02.05.2018 / 00:39

1 answer

Copy content button, and display a message shortly after you click it to copy Hide part of string

score 4 · Accepted Answer

Try the following, Where DuplicateColumn would be the column that has John 5x and TableName would be your table

SELECT NomeColunaDuplicada, Count(*) as QtdRepeticoes FROM NomeTabela
GROUP BY NomeColunaDuplicada
HAVING Count(*) > 1
order by QtdRepeticoes desc;

This select will bring the record repeated and how many times it was repeated in descending order equal below:

Soon what has been repeated will be first, but if you want to return only use it:

SELECT NomeColunaDuplicada, Count(*) as QtdRepeticoes FROM NomeTabela
GROUP BY NomeColunaDuplicada
HAVING Count(*) > 1
order by QtdRepeticoes desc
limit 1;