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 ?

OMG Ponies · Accepted Answer · 2010-10-19 03:07:34Z

26

Use:

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

answered Oct 19, 2010 at 3:07

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandra · Accepted Answer · 2019-11-12 11:24:15Z

1

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.

edited Nov 12, 2019 at 11:24

Sandra

3906 silver badges17 bronze badges

answered Jun 13, 2012 at 12:45

Manu

3,6453 gold badges33 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to sort ENUM column in MySQL database?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related