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 

Answer 2

This works fine with mysql. But for h2 DB it throws an error

Caused by: org.h2.jdbc.JdbcSQLException: Order by expression "CASEWHEN((color = 'YELLOW'), 1, CASEWHEN((color = 'RED'),3))" must be in the result list in this case; SQL statement:

To avoid the error add the stmt CASEWHEN((color = 'YELLOW'), 1, CASEWHEN((color = 'RED'),3)) in the select clause.