SELECT `id`, `n_a`, `cnt`
FROM `testtable2`
JOIN (
SELECT `n_a` AS `n_a2` , COUNT( * ) AS `cnt`
FROM `testtable2`
GROUP BY `n_a`
) `tmp` ON `n_a` =`n_a2`
ORDER BY `cnt` DESC+----+-------+-----+
| id | n_a | cnt |
+----+-------+-----+
| 3 | beta | 4 |
| 8 | beta | 4 |
| 5 | beta | 4 |
| 6 | beta | 4 |
| 7 | alpha | 2 |
| 1 | alpha | 2 |
| 4 | gamma | 1 |
| 2 | delta | 1 |