Q

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.


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 last published in May 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close