MySQL 按重复顺序排序

Question

我有一个lammer问题，因为我不是mysql专家

我有这样的字段

   id    color
   1     red
   2     green
   3     yellow
   4     green
   5     green
   6     red

我想按重复项分组，最常见的重复项优先所以应该这样选择:

   id    color
   2     green
   4     green
   5     green
   1     red
   6     red
   3     yellow

谢谢

Answer 1

"...most frequent duplicate first."

查询，

SELECT  a.*
FROM    TableName a
        INNER JOIN 
        (
            SELECT  Color, COUNT(*) totalCount
            FROM    TableName
            GROUP   BY Color
        ) b ON  a.Color = b.Color
ORDER   BY b.TotalCount DESC, a.ID ASC

• SQLFiddle Demo

输出

╔════╦════════╗
║ ID ║ COLOR  ║
╠════╬════════╣
║  2 ║ green  ║
║  4 ║ green  ║
║  5 ║ green  ║
║  1 ║ red    ║
║  6 ║ red    ║
║  3 ║ yellow ║
╚════╩════════╝