How to order unions separately in db_select? 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-&gt;fields('t1',array('id','fname');
$subquery1-&gt;where('fname=:fname',array(':fname'=&gt;'Frank');

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

$subquery1-&gt;union($subquery2);

$result = $subquery1-&gt;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
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-&gt;fields('t1',array('id','fname');
$subquery1-&gt;where('fname=:fname',array(':fname'=&gt;'Frank');
$subquery1-&gt;addExpression("'0_'",'sort_column');

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

$subquery1-&gt;union($subquery2);

$result = $subquery1-&gt;execute();
...