mysql - Order by duplicates top first with limit -

this question has answer here:

• grouping top n in mysql 2 answers

i have asked similar question here mysql order duplicates top first

how limiting duplicates, not more 2 distinct

i have fields like

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

i want group duplicates , , frequent duplicate first should selected way:

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

thank guys

there logical problem. "distinct" not working, because query result row unique in combination of "id" , "color" value. works if remove id column column list in select. otherwise use can use aggregat function "max/min/avg/count" - prefer max/min function - @ least 1 of ids?

if want count colors, try this:

select count(color) number_of_colors, color table group color order count(color) desc