How to sort ENUM column in MySQL database ?

Question

I have color column in MySQL table which type is ENUM('RED', 'YELLOW', 'MY_COLOR', 'BLACK'), and another name column which type is VARCHAR(30).

I would like to get all table rows in the following order:

• YELLOW rows first, sorted by name
• RED rows last, sorted by name
• In the middle, all other rows, sorted by name

Is that possible to make this kind of sort in 1 query ?

Answer 1

Use:

ORDER BY CASE color
           WHEN 'YELLOW' THEN 1
           WHEN 'RED' THEN 3
           ELSE 2
         END, name