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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close