How to Order by enum in Oracle DB

database, enums, oracle, sql, sql-order-by

Solution

You can enumerate the values in a `CASE` statement and order by that

SELECT id, name, status
  FROM your_table
 ORDER BY (CASE status 
                WHEN 'WAITING_FOR_APPROVAL' THEN 1
                WHEN 'ACTIVE' THEN 2
                WHEN 'DEACTIVE' THEN 3
                WHEN 'REJECTED' THEN 4
                ELSE 5
            END)

Problem

I want to order by a string column where that column is an enumeration. For example: ``` +----+--------+----------------------+ | ID | NAME | STATUS | +----+--------+----------------------+ | 1 | Serdar | ACTIVE | | 2 | John | DEACTIVE | | 3 | Jerry | WAITING_FOR_APPROVAL | | 4 | Jessie | REJECTED | +----+--------+----------------------+ ``` I want to order by `STATUS`. It should sort the results such that the first result must have STATUS = WAITING_FOR_APPROVAL, then ACTIVE, then DEACTIVE and then REJECTED. Is there any way to do that in SQL? Is there something like Comparator in java?

Original source