CODEWITHSUNDEEP
mysqlsqlgroup-by
Monster
Monster

Reputation: 1583

MySQL GROUP BY query 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

Upvotes: 1

Views: 90

Answers (3)

Joe Phillips
Joe Phillips

Reputation: 51200

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

Upvotes: 1

Josef Pfleger
Josef Pfleger

Reputation: 74557

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

Upvotes: 0

ariel
ariel

Reputation: 16140

Order by (ASSOCIATEDCOLOR = COLOR), ASSOCIATEDCOLOR

Upvotes: 4

Related Questions