Special sort order in a UNION query

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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top