MySQL GROUP BY query question

Question

I have a table that contains a name of a color (teal, for example) and the associated primary color (blue). Sometimes a color entry can be the same as its associated primary color (red, red).

How can I do a GROUP BY PRIMARY_COLOR where the COLOR for each group are in alphabetical order...except when a COLOR and PRIMARY_COLOR match -- this entry needs to be at the top of the grouping.

Example:

COLOR    PRIMARY_COLOR
------------------------
teal     blue
magma    red
sky      blue
red      red
magenta  red

should result...

COLOR    PRIMARY_COLOR
------------------------
sky      blue
teal     blue
red      red
magenta  red
magma    red

are you sure you need a group by? i think you are just doing an order by — il_guru
– il_guru, Commented May 6, 2011 at 21:39

ariel · Accepted Answer · 2011-05-06 21:34:45Z

4

Order by (ASSOCIATEDCOLOR = COLOR), ASSOCIATEDCOLOR

answered May 6, 2011 at 21:34

ariel

16.2k13 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Phillips · Accepted Answer · 2011-05-06 21:52:38Z

1

ORDER BY CASE WHEN COLOR=PRIMARY_COLOR THEN 0 ELSE 1 END, PRIMARY_COLOR

edited May 6, 2011 at 21:52

answered May 6, 2011 at 21:39

Joe Phillips

51.5k33 gold badges109 silver badges167 bronze badges

Comments

Josef Pfleger · Accepted Answer · 2011-05-06 21:40:01Z

0

SELECT color, primary_color
FROM colors
ORDER BY
    primary_color,
    color LIKE primary_color DESC,
    color

answered May 6, 2011 at 21:40

Josef Pfleger

74.6k16 gold badges99 silver badges100 bronze badges

Collectives™ on Stack Overflow

MySQL GROUP BY query question

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related