MySQL order by duplicates top first

Question

I have a lammer question, as far as I am not pro at mysql

I have fields like

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

I want to group by duplicates , and most frequent duplicate first so it should be selected this way:

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

thanks

Answer 1

"...most frequent duplicate first."

query,

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

OUTPUT

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

Answer 2

SELECT ID, COLOR
FROM MYTABLE A
JOIN
(
SELECT COLOR, COUNT(*) CN
FROM MYTABLE
GROUP BY COLOR
) B
ON B.COLOR = A.COLOR
ORDER BY B.CN, A.ID