MySQL ORDER BY 'ENUM' type value based on conditions

For this, use ORDER BY CASE statement. Let us first create a table, wherein we have ENUM type column −

mysql> create table DemoTable1461
   -> (
   -> DeckOfCards ENUM('K','J','A','Q')
   -> );
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1461 values('K');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1461 values('A');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1461 values('J');
Query OK, 1 row affected (0.44 sec)
mysql> insert into DemoTable1461 values('Q');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1461;

This will produce the following output −

+-------------+
| DeckOfCards |
+-------------+
| K           |
| A           |
| J           |
| Q           |
+-------------+
4 rows in set (0.00 sec)

Following is the query to order by ENUM type value −

mysql> select * from DemoTable1461
   -> order by
   -> case DeckOfCards when 'A' then 100
   -> when 'K' then 101
   -> when 'Q' then 102
   -> else 103
   -> end;

This will produce the following output −

+-------------+
| DeckOfCards |
+-------------+
| A           |
| K           |
| Q           |
| J           |
+-------------+
4 rows in set (0.00 sec)

AmitDiwan

Updated on 10-Dec-2019 06:56:49

• Related Questions & Answers
• ORDER BY records in MySQL based on a condition
• Replace records based on conditions in MySQL?
• How can I insert default value in MySQL ENUM data type?
• MySQL ORDER BY with custom field value
• How to sort by value with MySQL ORDER BY?
• Update quantity in MongoDB based on two conditions?
• Order by date set with varchar type in MySQL
• MySQL query with two boolean conditions to extract date based on hour?
• MySQL update a column with an int based on order?
• MySQL order by from highest to lowest value?
• ORDER BY specific field value first in MySQL
• What is MySQL ENUM data type? What are the advantages to use ENUM data type?
• Order by on a specific number in MySQL?
• Add a new value to a column of data type enum in MySQL?
• How to sum based on field value in MySQL?