How To Sort Enum Column In Mysql Database?

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 i

Solution 1:

Use:

ORDERBYCASE color
           WHEN'YELLOW' THEN 1WHEN'RED' THEN 3ELSE2END, name 

Solution 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.