SELECT Name, COUNT(*) AS Repeats
FROM city
GROUP BY Name
HAVING COUNT(*) > 1
ORDER BY Repeats DESC, Name
| Name |
Repeats |
| San José |
4 |
| Cambridge |
3 |
| Córdoba |
3 |
| Hamilton |
3 |
| La Paz |
3 |
| León |
3 |
| Richmond |
3 |
| San Fernando |
3 |
| San Miguel |
3 |
| Springfield |
3 |
| ... |
| GROUP BY gathers rows with the same Name into groups, and HAVING COUNT(*) > 1 keeps only the groups with more than one row. The result is every value that occurs more than once, with the number of repeats next to it. The other examples in this section return the whole rows with such values. |