How to order unions separately in db_select?

Question

I have a UNION query, where the second part of the UNION should be ordered separately, not the complete UNION.

The resulting query should look like this:

(
  SELECT id, fname FROM tab1 WHERE fname = 'Frank'
) UNION (
  SELECT id, fname FROM tab2 WHERE lname = 'Sinatra'
  ORDER BY fname DESC
)

My question is: How can I order the second part of the UNION? I know, I could use a db_query, but I'd like to join this db_select in another db_select. And I think I cannot join an executed db_query result and a db_select object.

Here is my current code:

$subquery1 = db_select('tab1','t1');
$subquery1->fields('t1',array('id','fname');
$subquery1->where('fname=:fname',array(':fname'=>'Frank');

$subquery2 = db_select('tab2','t2');
$subquery2->fields('t2',array('id','fname');
$subquery2->where('lname=:lname',array(':lname'=>'Sinatra');
$subquery2->orderBy('fname','DESC');

$subquery1->union($subquery2);

$result = $subquery1->execute();
...

The resulting query is:

(
  SELECT id, fname FROM tab1 WHERE fname = 'Frank'
) UNION (
  SELECT id, fname FROM tab2 WHERE lname = 'Sinatra'
)
ORDER BY fname DESC

Answer 1

I found the answer not in Drupal, but in MySQL:

I added a new column to the subqueries with an indicator of the query. The first sub query has the sort column value "0_" and the second one "1_".

Here is the drupal db_select code:

$subquery1 = db_select('tab1','t1');
$subquery1->fields('t1',array('id','fname');
$subquery1->where('fname=:fname',array(':fname'=>'Frank');
$subquery1->addExpression("'0_'",'sort_column');

$subquery2 = db_select('tab2','t2');
$subquery2->fields('t2',array('id','fname');
$subquery2->where('lname=:lname',array(':lname'=>'Sinatra');
$subquery2->addExpression('1_','sort_column');
$subquery2->orderBy('sort_column', 'ASC');
$subquery2->orderBy('fname','DESC');

$subquery1->union($subquery2);

$result = $subquery1->execute();
...

Answer 2

I don't know this will help, but you can try:

$subquery1 = db_select('tab1','t1');
$subquery1->fields('t1',array('id','fname');
$subquery1->condition('t1.fname', 'Frank')

$subquery2 = db_select('tab2','t2');
$subquery2->fields('t2',array('id','fname');
$subquery1->condition('t2.lname', 'Sinatra')
          ->orderBy('t2.fname','DESC');

$query = Database::getConnection()
  ->select($subquery1->union($subquery2))
  ->fields(NULL, array('id','fname'))