SELECT * FROM table a LEFT JOIN (SELECT key,count(*) as c FROM table GROUP BY key ) b on a.key=b.key ORDER BY c desc