How to use order by with union all in sql?

Question

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?

score 143 · Accepted Answer · answered Mar 18 '13 at 05:19

143

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

answered Mar 18 '13 at 05:19

John Woo

258,903
69
498
492

@JohnWoo - what does 'dum' do here? – DJC Jul 31 '17 at 10:30
@CIvemy `dum` is a table alias. – John Woo Aug 01 '17 at 02:35
1

Doesn't work if you are using TOP. Look at the answer provided by @Fandango68 – Code Novice Jan 17 '20 at 18:22
One additional note: There's a rule that in the query SELECT * FROM X, no matter what you swap in for X, the rows of X are never guaranteed to be in any particular order. T-SQL won't let you swap in (SELECT * FROM TABLE_A ORDER BY COLUMN_1) for X because the ORDER BY clause won't accomplish anything at all. Now, you can swap in (SELECT TOP(10) * FROM TABLE_A ORDER BY COLUMN_1) for X, and if you do then you'll get the top ten rows, but they might be in the wrong order. – Aulimaitar Jun 29 '23 at 21:24

score 59 · Answer 2 · answered May 25 '15 at 15:16

59

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

answered May 25 '15 at 15:16

Giorgi Nakeuri

35,155
8
47
75

1

Is this documented somewhere? I found [Example C in the UNION doc](https://msdn.microsoft.com/en-us/library/ms180026.aspx), but it shows that it can be done, and does not explain it. – Trisped Dec 21 '15 at 18:11
1

@Trisped, `from where group having select order` it is the logical processing sequence. so ordering is applied in the end is legal here. https://msdn.microsoft.com/en-us/library/ms189499.aspx – Giorgi Nakeuri Dec 21 '15 at 18:27
1

Worth noting that you have to have put all the columns you're ordering by within each of your SELECT clauses. – HartleySan Sep 16 '17 at 02:45
1

I wouldn't call this the right answer because you're selecting one extra column (RN). You don't always have the option/luxury of having arbitrary columns in your result set. – pedram bashiri Sep 25 '17 at 19:08
1

@pedrambashiri There is no need to select an extra column. I guess it's only here for demo purposes. As HartleySan noted you must name the column(s) to sort on in both SELECT clauses. By using an alias (the RN column) you can sort even if the columns have different names in both tables. – ArieKanarie Jul 04 '18 at 08:56
It makes a great deal to have an extra column to make sure the return row is in order! – zionpi Aug 24 '22 at 08:35

Fandango68 · Answer 3 · 2018-02-01T02:34:31.427

38

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.

edited Feb 01 '18 at 02:34

answered Jun 20 '17 at 05:43

Fandango68

4,461
4
39
74

2

Perfect. Oracle allows you to sort anywhere... new to SQL Server... using TOP is great however I find it's functionality very lacking in more complex queries... I keep getting the dang ORDER BY error the OP mentioned... just let me ORDER the dang results lol. – Code Novice Jan 17 '20 at 18:22
2

You can use `TOP 100 PERCENT` clause. Using arbitrary number believing it will be always larger than number of returned records is bad practice. – keiichi Apr 22 '20 at 12:31
@keiichi this is why TOP 100 is not a good idea https://stackoverflow.com/questions/1622878/why-use-select-top-100-percent – Fandango68 Apr 23 '20 at 03:50

MarwanAbu · Answer 4 · 2022-04-01T08:16:23.453

1

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 .

edited Apr 01 '22 at 08:16

answered Apr 01 '22 at 08:07

MarwanAbu

181
8

It's the same as the accepted answer – Santiago Apr 03 '22 at 15:44

score 0 · Answer 5 · edited Apr 17 '21 at 22:07

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.

score 0 · Answer 6 · edited Mar 27 '22 at 20:00

0

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

edited Mar 27 '22 at 20:00

RF1991

2,037
4
8
17

answered May 14 '21 at 19:58

Sushmita Sharma

1

score 0 · Answer 7 · answered Jun 20 '22 at 08:49

0

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

answered Jun 20 '22 at 08:49

Hasan Ali Haolader

39
5

score -1 · Answer 8 · answered Aug 08 '22 at 09:17

-1

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

answered Aug 08 '22 at 09:17

Efrat Ifergan

184
1
5

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 10 '22 at 13:56

score -3 · Answer 9 · answered Oct 13 '20 at 13:36

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

How to use order by with union all in sql?

9 Answers9

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

Linked