How to Find ALL Most Common Values in SQL?

Question

How would I go about showing ALL most common values in SQL?

So I have the query to show the most common value here bellow.

SELECT name, COUNT(*) AS popularity
FROM cattwo 
GROUP BY name 
ORDER BY popularity DESC 
LIMIT 1;

    +----------+------------+
    | name     | popularity |
    +----------+------------+
    | cat22610 |          7 |
    +----------+------------+

However, when I display the top 10 most common value the result is ...

SELECT name, COUNT(*) AS popularity 
FROM cattwo 
GROUP BY name 
ORDER BY popularity DESC 
LIMIT 10;

    +----------+------------+
    | name     | popularity |
    +----------+------------+
    | cat22610 |          7 |
    | cat68704 |          7 |
    | cat14153 |          7 |
    | cat52476 |          7 |
    | cat4556  |          7 |
    | cat64173 |          7 |
    | cat5586  |          7 |
    | cat89302 |          6 |
    | cat97131 |          6 |
    | cat42010 |          6 |
    +----------+------------+

The goal is to display all cats with the highest popularity. Something like this.

+----------+------------+
| name     | popularity |
+----------+------------+
| cat22610 |          7 |
| cat68704 |          7 |
| cat14153 |          7 |
| cat52476 |          7 |
| cat4556  |          7 |
| cat64173 |          7 |
| cat5586  |          7 |
+----------+------------+

Help would be great. Thanks in advance.

Answer 1

I guess this query would help you!

SELECT NAME, COUNT(*) AS POPULARITY 
FROM CATTWO 
GROUP BY NAME 
HAVING COUNT(*) = 
        (
            SELECT COUNT(*) AS MAX_POPULARITY 
            FROM CATTWO 
            GROUP BY NAME
            ORDER BY MAX_POPULARITY DESC
            LIMIT 1
        );