How can I use the MySQL FIELD() function within a SelectQuery object in Views?
I got this working! I couldn't find any way to get the orderBy working in hook_views_query_alter(), so I tried to do this in the raw hook_query_alter(). In my particular situation, I couldn't get any result when I filtered against the table that Views had already joined with (I can't explain why) and so I added my own join. Voila, it works.

function mymodule_query_alter($query) {
  if ($query-&gt;hasTag('my_view_id')) {
    // Remove the current sort order. 
    $order =&amp; $query-&gt;getOrderBy();
    $order = array();

    // Join with the table that we need, giving it a custom alias.
    $query-&gt;join('field_data_field_listing_status', 'custom_status', 'custom_status.entity_id = node.nid');

    // Add our cool FIELD() function to push all 'sold' nodes to the very back of the list.
    $query-&gt;orderBy("FIELD(custom_status.field_listing_status_value, 'sold')", 'ASC');

    // Add any other ordering that we need...
    $query-&gt;orderBy('node_created', 'DESC');
  }
}