Does Big Query support custom sorting?

I am trying to sort data by applying case when statement in the order by clause but looks like Big Query doesn't support even though it worked fine in other SQL environments. Can somebody share your thoughts on this.

What is BigQuery not good for?

You need to understand that BigQuery cannot be used to substitute a relational database, and it is oriented on running analytical queries, not for simple CRUD operations and queries.

Which of these SQL modes is supported in BigQuery?

BigQuery supports the Google Standard SQL dialect, but a legacy SQL dialect is also available. If you are new to BigQuery, you should use Google Standard SQL as it supports the broadest range of functionality. For example, features such as DDL and DML statements are only supported using Google Standard SQL.

select x 
from (
  select x ,
  case when x = 'a' then 'z' else x end as y
  from 
    (select 'a' as x),
    (select 'b' as x),
    (select 'c' as x),
    (select 'd' as x)
  )
order by y desc