sql ORDER BY multiple values in specific order?

Solution 1:

...
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

Solution 2:

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.

Solution 3:

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.

Solution 4:

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.