Ask the Expert

Special sort order in a UNION query

I have a table called TTEST with these columns and data:

col1  col2    col3    col4        col5
  1   ABCD    TEST3   1998-04-28   1.11
  2   EFGH    TEST2   1999-03-04   2.22
  3   WXYZ    TEST1   1996-12-31   3.33
  5   5TEST   TEST5   NULL        55.55
 10   10TEST  TEST10  NULL        10.1
 20   20TEST  TEST20  NULL        20.2
 30   30TEST  TEST30  NULL        30.3
 90   ZYX     TEST99  2004-04-03  90.9
 95   WVU     TEST98  2004-05-05  95.95
 98   TSR     TEST95  2004-05-06  98.98
 99   CBA     TEST90  2004-03-04  99.99

If I write two queries like this:

select * from TTEST 
where col1 < 5 and col3 like 'test%' 
order by col3

col1  col2    col3    col4        col5
  3   WXYZ    TEST1   1996-12-31   3.33
  2   EFGH    TEST2   1999-03-04   2.22
  1   ABCD    TEST3   1998-04-28   1.11


select * from TTEST 
where col1 > 90 
order by col2

col1  col2    col3    col4        col5
 99   CBA     TEST90  2004-03-04  99.99
 98   TSR     TEST95  2004-05-06  98.98
 95   WVU     TEST98  2004-05-05  95.95

My question is, can I JOIN or UNION to get result set 1 and result set 2 using a single query in the same order as this:

col1  col2    col3    col4        col5
  3   WXYZ    TEST1   1996-12-31   3.33
  2   EFGH    TEST2   1999-03-04   2.22
  1   ABCD    TEST3   1998-04-28   1.11
 99   CBA     TEST90  2004-03-04  99.99
 98   TSR     TEST95  2004-05-06  98.98
 95   WVU     TEST98  2004-05-05  95.95

Appreciate your help.


    Requires Free Membership to View

Yes, with UNION. However, you can't have an ORDER BY for each query in the UNION, because a UNION query can have only one ORDER BY.

The trick is to create two additional columns in each SELECT, for the purpose of sorting. Remember, the ORDER BY in a UNION sorts all rows from the combined result set.

select TTEST.*
     , 1       as sourcequery
     , col3    as sortcolumn 
  from TTEST 
 where col1 < 5 and col3 like 'test%' 
union all
select TTEST.*
     , 2      
     , col2    
 where col1 > 90 
order 
    by sourcequery
     , sortcolumn

For More Information


This was first published in May 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: