How to sort a column using a custom order in amazon athena?

Question

to sort this column

abc

to get this output

bac

the mysql clause order by field(col,'b','a','c') does not work in athena. is there an equivalent?

Accepted Answer

You can use a case when for getting this order:

ORDER BY CASE WHEN col='b' THEN 1WHEN col='a' THEN 2WHEN col='c' THEN 3ELSE 4 END ASC