sql order by

SELECT * FROM users
where prefecture_id = 13
and deleted_at is not null
order by
created_at > current_time() desc,
created_at != '0000-00-00 00:00:00' asc,
created_at asc
;

created_at > current_time() の結果を0,1 で判断し、desc or asc で並び替える