Find the most frequent value in mysql,display all in case of a tie

Question

For example, we have 1, 2 and 3 are the most frequent values at the same time, how to return them when it is a tie?

id
1
1
1
2
2
2
3
3
3
4

Answer 1

You could try:

SELECT id
FROM yourTable
GROUP BY id
HAVING COUNT(*) = (SELECT COUNT(*) FROM yourTable
                   GROUP BY id ORDER BY COUNT(*) DESC LIMIT 1);

On more recent versions of MySQL 8+, we can use RANK here:

WITH cte AS (
    SELECT id, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM yourTable
    GROUP BY id
)

SELECT id
FROM cte
WHERE rnk = 1;