How to use order by with union all in sql?

I tried the sql query given below: 

SELECT * FROM (SELECT *
FROM TABLE_A ORDER BY COLUMN_1)DUMMY_TABLE
UNION ALL
SELECT * FROM TABLE_B

It results in the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I need to use order by in union all. How do I accomplish this?

185044 次浏览
小开
最佳答案 
SELECT  *
FROM
(
SELECT * FROM TABLE_A
UNION ALL
SELECT * FROM TABLE_B
) dum
-- ORDER BY .....

but if you want to have all records from Table_A on the top of the result list, the you can add user define value which you can use for ordering,

SELECT  *
FROM
(
SELECT *, 1 sortby FROM TABLE_A
UNION ALL
SELECT *, 2 sortby FROM TABLE_B
) dum
ORDER   BY sortby
小开

You don't really need to have parenthesis. You can sort directly:

SELECT *, 1 AS RN FROM TABLE_A
UNION ALL
SELECT *, 2 AS RN FROM TABLE_B
ORDER BY RN, COLUMN_1
小开

Not an OP direct response, but I thought I would jimmy in here responding to the the OP's ERROR messsage, which may point you in another direction entirely!

All these answers are referring to an overall ORDER BY once the record set has been retrieved and you sort the lot.

What if you want to ORDER BY each portion of the UNION independantly, and still have them "joined" in the same SELECT?

SELECT pass1.* FROM
(SELECT TOP 1000 tblA.ID, tblA.CustomerName
FROM TABLE_A AS tblA ORDER BY 2) AS pass1
UNION ALL
SELECT pass2.* FROM
(SELECT TOP 1000 tblB.ID, tblB.CustomerName
FROM TABLE_B AS tblB ORDER BY 2) AS pass2

Note the TOP 1000 is an arbitary number. Use a big enough number to capture all of the data you require.

小开 
select CONCAT(Name, '(',substr(occupation, 1, 1), ')') AS f1
from OCCUPATIONS
union
select temp.str AS f1 from
(select count(occupation) AS counts, occupation, concat('There are a total of ' ,count(occupation) ,' ', lower(occupation),'s.') As str  from OCCUPATIONS group by occupation order by counts ASC, occupation ASC
) As temp
order by f1
小开

There will be times when you need to do something like this :

Pull top 5 from table 1 based on a sort
and bottom 5 from table 2 based on another sort
and union these together.

solution

select * from (
-- top 5 records
select top 5 col1, col2, col3
from table1
group by col1, col2
order by col3 desc ) z


union all


select * from (
-- bottom 5 records
select top 5 col1, col2, col3
from table2
group by col1, col2
order by col3 ) z

this was the only way i was able to get around the error and worked fine for me.

小开 
SELECT * FROM (SELECT *
FROM TABLE_A ORDER BY COLUMN_1)DUMMY_TABLE
UNION ALL
SELECT * FROM TABLE_B
ORDER BY 2;

2 is column number here .. In Oracle SQL you can use the column number by which you want to sort the data

小开

This solved my SELECT statement:

SELECT * FROM
(SELECT id,name FROM TABLE_A
UNION ALL
SELECT id,name FROM TABLE_B )  dum
order by dum.id , dum.name

where id and name columns available in tables and you can use your columns .

小开

Simply use that , no need parenthesis or anything else

SELECT *, id as TABLE_A_ID FROM TABLE_A
UNION ALL
SELECT *, id as TABLE_B_ID FROM TABLE_B
ORDER BY TABLE_A_ID, TABLE_B_ID
小开

ORDER BY after the last UNION should apply to both datasets joined by union.

The solution shown below:

SELECT *,id AS sameColumn1 FROM Locations
UNION ALL
SELECT *,id AS sameColumn2 FROM Cities
ORDER BY sameColumn1,sameColumn2

提交答案