How to sort categorical values by an arbitrary order? I have a table called ANIMALS which looks like this

| name      | category |
------------------------
| fluffer   | CAT      |
| tooter    | ELEPHANT |
| tooter_jr | ELEPHANT |
| woofer    | DOG      |


If I sort the table by category then I will get all CATs then DOGs then ELEPHANTs. But I need to order the table such that the sequence is CAT > ELEPHANT > DOG

How can I achieve this result?
This can be done using a CASE statement in the ORDER BY clause:

WITH
Animals (name, category) AS (
 SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
 SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
  name, category
FROM Animals
ORDER BY
  CASE category
  WHEN 'CAT' THEN 1
  WHEN 'ELEPHANT' THEN 2
  WHEN 'DOG' THEN 3
  END
;


Please comment, if and as this requires adjustment / further detail.