bjelleklang bjelleklang - 3 months ago 22
SQL Question

Oracle - Order by specific values first, then the rest

I've got an Oracle 11g table with a column indicating statuses for a number of items. Is there any way to do a select where three specific flags are ordered first, and then the rest in alphabetical order?

Something similar to

SELECT ITEM, STATUS FROM FOO ORDER BY STATUS ('I', 'U', 'P') ASC


which would then list all items with status I, then U, then P, and then the remaining items last.

Answer

This works for all DB engines

SELECT ITEM, STATUS 
FROM FOO 
ORDER BY case when STATUS = 'I' then 1
              when STATUS = 'U' then 2
              when STATUS = 'P' then 3
              else 4
         end,
         status