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 ?

Answers


Use:

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.


Need Your Help

Custom MembershipProvider with Web interface and DAL

asp.net linq membership asp.net-membership custom-membershipprovider

I'm working on an ASP.NET solution with 2 projects. One is the web interface and the other contains my business logic. I'm using LINQ to SQL for my data access in the second project.

SqlException 'invalid column name' for composite key tables with Entity Framework

c# asp.net sql asp.net-mvc entity-framework

I have sql tables with composite keys that I'm trying to pull into EF Code First models, but I'm getting 'invalid column name' errors. A typical schema in sql server management studio might look like