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 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 ?
ORDER BY CASE color WHEN 'YELLOW' THEN 1 WHEN 'RED' THEN 3 ELSE 2 END, name
This works fine with mysql. But for h2 DB it trhows 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.