CODEWITHSUNDEEP
sql
ElenA
ElenA

Reputation: 78

How to use union if i need to "order by" all selects

i have 3 separate select statements that i need to union. but all of them need to be ordered by a different column. i tried doing this 

select * from(
select * from (select columns from table1 order by column1 ) A
UNION
select * from (select columns from table2 order by column2 ) B
UNION
select * from (select columns from table3 order by column3 ) C
) Table

but this doesn't work does anyone have any experience with this?

Upvotes: 1

Views: 48

Answers (3)

granadaCoder
granadaCoder

Reputation: 27842

You have to order it AFTER the UNION's.

You can "trick it" like this:

select Artificial, a,b,c from(
select 1 as Artificial, a,b,c from (select columns from table1  ) A
UNION
select 2 as Artificial,a,b,c from (select columns from table2  ) B
UNION
select 3 as Artificial,a,b,c from (select columns from table3  ) C
) derivedTable

order by Artificial, c,b,a

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do something like this:

select *
from((select columns, 'table1' as which from table1  )
     UNION ALL
     (select columns, 'table2' from table2 )
     UNION ALL
     (select columns, 'table3' from table3 )
    ) t
order by which,
         (case when which = 'table1' then column1
               when which = 'table2' then column2
               when which = 'table3' then column3
          end);

This assumes that the columns used for ordering are all of the same type.

Note that this query uses union all instead of union. I see no reason why you would want to eliminate duplicates if you want the results from the three subqueries ordered independently.

EDIT:

You can also express the order by separately for each table:

order by which,
         (case when which = 'table1' then column1 end) ASC,
         (case when which = 'table2' then column2 end) DESC
         (case when which = 'table3' then column3 end)

Upvotes: 2

valex
valex

Reputation: 24134

You should separate these columns in the one common column and then order 

SELECT * FROM
(
  SELECT A.*,columnA as ORDER_COL FROM A
  UNION ALL
  SELECT B.*,columnB as ORDER_COL FROM B
  UNION ALL
  SELECT C.*,columnC as ORDER_COL FROM C
) as T1 
ORDER BY ORDER_COL

Upvotes: 1

Related Questions