sql ORDER BY multiple values in specific order?

Question

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

Try:

ORDER BY x_field='f', x_field='p', x_field='i', x_field='a'

You were on the right track, but by putting x_field only on the 'f' value, the other three were treated as constants and not compared against anything in the dataset.

You can use a LEFT JOIN with a "VALUES ('f',1),('p',2),('a',3),('i',4)" and use the second column in your order-by expression. Postgres will use a Hash Join which will be much faster than a huge CASE if you have a lot of values. And it is easier to autogenerate.

If this ordering information is fixed, then it should have its own table.

I found a much cleaner solution for this:

ORDER BY array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field)

Note: array_position needs Postgres v9.5 or higher.

Use a case switch to translate the codes into numbers that can be sorted:

ORDER BY
  case x_field
  when 'f' then 1
  when 'p' then 2
  when 'i' then 3
  when 'a' then 4
  else 5
  end

The CASE and ORDER BY suggestions should all work, but I'm going to suggest a horse of a different color. Assuming that there are only a reasonable number of values for x_field and you already know what they are, create an enumerated type with F, P, A, and I as the values (plus whatever other possible values apply). Enums will sort in the order implied by their CREATE statement. Also, you can use meaninful value names—your real application probably does and you have just masked them for confidentiality—without wasted space, since only the ordinal position is stored.

@bobflux's answer is great. I would like to extend it by adding a complete query that uses proposed approach.

select tt.id, tt.x_field
from target_table as tt
-- Here we join our target_table with order_table to specify custom ordering.
left join
    (values ('f', 1), ('p', 2), ('i', 3), ('a', 4)) as order_table (x_field, order_num)
    on order_table.x_field = tt.x_field
order by
    order_table.order_num, -- Here we order values by our custom order.
    tt.x_field;            -- Other values can be ordered alphabetically, for example.

Here is complete demo.