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.
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
);