Select by code and if there is no result select any other

Imagine there are some table with some data:

id | code | name
----------------
1  | 0001 | qwer
2  | 0002 | rtyu
3  | 0003 | asdf

There can be only one record with code == 0002. Also it may be absent.

How could I select record with code == 0002 and if there is no such record then select any other record with single query?

For example for the data example above query returns record with id == 2. And for the example below:

id | code | name
----------------
1  | 0001 | qwer
2  | 0004 | rtyu
3  | 0003 | asdf

any other (one)?

Answer

One method uses order by and limiting the results to one row:

select t.*
from t
order by (case when code = '002' then 1 else 2 end)
fetch first 1 row only;

This uses Standard SQL. Not all databases support fetch first, so you might need to use limit or select top or something else.