SQL: Order by customized sequence of values

Lets say that you have the following SQL statement.

SELECT DEPARTMENT, STATUS, NAME FROM EVENT ORDER BY DEPARTMENT, STATUS;

AND the STATUS column has such values: Initial, data collected, processing, completed. If you would like the records returned by your SQL statement to be ordered by the DEPARTMENT in alphabetic order and with each department, the events are ordered by status as listed above. You cannot use ORDER BY STATUS to achieve it for the order you want is not alphabetic.

You can achieve it by using the following SQLs.

1. SELECT DEPARTMENT, STATUS, NAME
     FROM EVENT
     ORDER BY DEPARTMENT,
              CASE STATUS WHEN 'Initial' THEN 1
                                     WHEN 'data collected' THEN 2
                                     WHEN 'processing' THEN 3
                                     WHEN 'completed' THEN 4
              ELSE 5
              END  ASC;


2. For ORACLE

     SELECT DEPARTMENT, STATUS, NAME
     FROM EVENT
     ORDER BY DEPARTMENT,
                          instr('Initial, data collected, processing, completed', STATUS);


     For MySQL

     SELECT DEPARTMENT, STATUS, NAME
     FROM EVENT
     ORDER BY DEPARTMENT,
                          FIELD (STATUS, 'Initial, data collected, processing, completed');



                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.