How to order by with union in SQL?

Question

Is it possible to order when the data is come from many select and union it together? Such as

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"

How can I order this query by name?

I tried this

Select id,name,age
From Student
Where age < 15 or name like "%a%"
Order by name

But that does not work.

If you have the same column in union query then at the end put order by your column name. — anirban karak, Aug 06 '15 at 10:45

score 361 · Answer 1 · answered Jan 17 '11 at 17:15

361

Just write

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name

the order by is applied to the complete resultset

answered Jan 17 '11 at 17:15

bernd_k

11,558
7
45
64

63

What if I want the sort to be applied on only the top one of the UNION ? – marifrahman Dec 15 '15 at 05:59
8

@marifrahman see my answer http://stackoverflow.com/a/43855496/2340825 – BA TabNabber May 08 '17 at 19:13
5

@marifrahman sorry to dig an old topic, but it may help others. In case you want the ORDER BY to be applied to the first part of the UNION, protect this SELECT with parenthesis. – Lideln Kyoku Jan 10 '20 at 22:02
What if you don't want to have `name` be returned though? Can you provide an alias to both tables to `ORDER BY` but omit it from the resultset? – user2402616 Feb 25 '22 at 16:03

Mark Robinson · Answer 2 · 2014-03-11T16:33:52.897

110

Select id,name,age
from
(
   Select id,name,age
   From Student
   Where age < 15
  Union
   Select id,name,age
   From Student
   Where Name like "%a%"
) results
order by name

edited Mar 11 '14 at 16:33

answered Jan 17 '11 at 17:13

Mark Robinson

13,128
13
63
81

62

As bernd_k pointed out, by definition, the individual SELECTs making up a UNION are not allowed to contain an ORDER BY clause. The only ORDER BY clause allowed is at the end of the UNION and it applies to the entire UNION, making `xxx UNION yyy ORDER BY zzz` the eqivalent of `(xxx UNION yyy) ORDER BY zzz` – Nicholas Carey Jan 17 '11 at 18:15

score 64 · Answer 3 · edited Oct 07 '21 at 04:41

64

In order to make the sort apply to only the first statement in the UNION, you can put it in a subselect with UNION ALL (both of these appear to be necessary in Oracle):

Select id,name,age FROM 
(    
 Select id,name,age
 From Student
 Where age < 15
 Order by name
)
UNION ALL
Select id,name,age
From Student
Where Name like "%a%"

Or (addressing Nicholas Carey's comment) you can guarantee the top SELECT is ordered and results appear above the bottom SELECT like this:

Select id,name,age, 1 as rowOrder
From Student
Where age < 15
UNION
Select id,name,age, 2 as rowOrder
From Student
Where Name like "%a%"
Order by rowOrder, name

edited Oct 07 '21 at 04:41

nik7

806
3
12
20

answered May 08 '17 at 19:09

BA TabNabber

1,296
2
14
18

4

Yes. That orders the the results of the subselect. That does NOT order the results of the `select` statement referencing that subselect. Per the SQL Standard, the order of results is undefined barring an explicit `order by` clause. That first `select` in your example _probably_ returns its results in the order returned by the subselect, but it is not guaranteed. Further, that _does *not*_ guarantee the ordering of the result set of the entire `union` (same rule in the Standard). If you are depending on the order, you will — eventually — get bitten. – Nicholas Carey May 10 '17 at 20:36
1

@Nicholas Carey - when I initially tested using a UNION it was behaving unpredictably as you described, I think the UNION ALL (at least in Oracle) was necessary to order the top SELECT above the bottom. However I've provided an alternate that does guarantee correct ordering and should be database independent. – BA TabNabber May 10 '17 at 22:24
Not working for me. The one with UNION ALL still fails to maintain the order within the first `SELECT`. – Amit Chigadani Apr 18 '18 at 11:29
1

And the problem with the second query is, it does not eliminate the duplicate records. Because you have added another column 'rowOrder' which might have different value against the duplicate records. Purpose of UNION against UNION ALL is lost. – Amit Chigadani Apr 18 '18 at 12:20
2

@AmitChigadani Elimination of duplicates wasn't part of the original question, but to do so the WHERE clauses can be modified to ensure uniqueness. eg: Where Name like "%a%" AND age >= 15 – BA TabNabber Apr 19 '18 at 18:40
@BATabNabber Yes, I agree it is not part of the question but it's the feature of `UNION`. – Amit Chigadani Apr 20 '18 at 06:40
How to use this when your two select statements are on a #temp table? – Dieter Jun 13 '20 at 17:10
rowOrder worked in a minimal example for me as well while UNION ALL did not in MySQL 5.7.32. However as rowOrder has an additinoal column it is unusable in my use case. – merlin Dec 23 '20 at 19:36
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. – Zhang Dec 03 '21 at 05:30

score 26 · Answer 4 · edited May 18 '22 at 12:26

26

As other answers stated, ORDER BY after the last UNION should apply to both datasets joined by union.

I had two datasets using different tables but the same columns. ORDER BY after the last UNION still didn't work.

Using an alias for the column used in the ORDER BY clause did the trick.

SELECT Name, Address FROM Employee 
UNION
SELECT Customer_Name, Address FROM Customer
ORDER BY customer_name;   --Won't work

The solution was to use the alias User_Name, shown below:

SELECT Name AS User_Name, Address FROM Employee 
UNION
SELECT Customer_Name AS User_Name, Address FROM Customer
ORDER BY User_Name;

edited May 18 '22 at 12:26

Conrad37

166
1
17

answered Oct 11 '18 at 17:06

Mandrake

411
4
2

don't do the trick in spark SQL : `mismatched input 'FOR' expecting ` – Jérémy May 27 '21 at 14:09
1

maybe mistype, should be from, not for, in first line of both examples? – eep Apr 22 '22 at 17:56
This was the answer that helped me the most, as I was using an alias in my query but then trying to ORDER BY the original column name. It was the same column name in both parts of the UNION but because I had named it with an alias, it was looking for that specific alias I had used. So just a warning for others: if you use aliases in a UNION and want to ORDER BY that column, make sure you ORDER BY the Alias. – Conrad37 May 16 '22 at 14:24
In a UNION, the top section dictates the field names used in subsequent sections, so in your example, the top section uses "Name" and "Address", and the bottom section uses "Customer_Name" and "Address". The UNION treats the whole thing as if "Name" and "Address" using the top section as the guide. So ORDER BY Name ought to work because it'll recognise that from the top section, whereas your example ORDER BY Customer_Name doesn't work because it thinks the whole thing is called Name. – psymann Apr 17 '23 at 08:50
When a union operator is used whatever the column names we specify in the first select query only those column names will be appeared in the result set. since ORDER BY is performed on this result set we can do `ORDER BY` only on the column names of the first select query. so, in the above solution without alias we can just do an `ORDER BY` on Name column. Hope this helps!! – Vineeth Peddi Aug 18 '23 at 10:42

score 15 · Answer 5 · edited Sep 27 '20 at 09:29

15

Both other answers are correct, but I thought it worth noting that the place where I got stuck was not realizing that you'll need order by the alias and make sure that the alias is the same for both the selects... so

select 'foo'
union
select item as `foo`
from myTable
order by `foo`

notice that I'm using single quotes in the first select but backticks for the others.

That will get you the sorting you need.

edited Sep 27 '20 at 09:29

moonvader

19,761
18
67
116

answered Jun 03 '13 at 19:37

Yevgeny Simkin

27,946
39
137
236

what's the important you want to make with using single quote in first select and backticks in other? Ideally it should be consistent. – nanosoft Oct 17 '17 at 11:52
2

The first select is a literal; it's a header like 'NAMES'. The second select is a reference to a table. So your first row will say "NAMES" and the rest of the rows will be the actual names selected from the table. The point is that your header may very well be the same string as the name of the column from which you're selecting and this is the solution for using the label you want without it colliding in your union. – Yevgeny Simkin Oct 23 '17 at 06:36
2

After some experimentation I see that the alias mentioned in the ORDER BY clause must be mentioned in the SELECT clauses. You can't sort by another column. Of course you can work around that by wrapping the whole thing in a `SELECT a, b, c FROM () AS x;` if you really want to avoid returning the extra column. – Wodin Mar 14 '18 at 15:31

score 11 · Answer 6 · edited Aug 06 '15 at 10:29

11

Order By is applied after union, so just add an order by clause at the end of the statements:

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like '%a%'
Order By name

edited Aug 06 '15 at 10:29

SvenBM

3
1

answered Mar 09 '15 at 11:24

Sunil Kumar

655
1
7
12

score 9 · Answer 7 · edited Oct 07 '21 at 05:36

9

If I want the sort to be applied to only one of the UNION if use UNION ALL:

Select id,name,age
From Student
Where age < 15
Union all
Select id,name,age
From 
(
Select id,name,age
From Student
Where Name like "%a%"
Order by name
)

edited Oct 07 '21 at 05:36

nik7

806
3
12
20

answered Jul 01 '16 at 16:46

osydorchuk

143
2
12

Edward · Answer 8 · 2021-09-14T08:44:43.110

3

To add to an old topic, I used ROW_NUMBER (using MS SQL). This allows sorts (orders) within UNIONs. So using an idea from @BATabNabber to separate each half of the Union, and @Wodin of wrapping the whole thing in a select, I got:

Select Id, Name, Age from
(
Select Id, Name, Age, 1 as Mainsort
 , ROW_NUMBER() over (order by age) as RowNumber
From Student
Where Age < 15

Union

Select Id, Name, Age, 2 as Mainsort
 , ROW_NUMBER() over (Order by Name) as RowNumber
From Student
Where Name like '%a%'
) as x
Order by Mainsort, RowNumber

So adjust, or omit, what you want to Order by, and add Descendings as you see fit.

edited Sep 14 '21 at 08:44

answered Sep 13 '21 at 12:07

Edward

79
1
7

Please add code and data as text ([using code formatting](//stackoverflow.com/editing-help#code)), not images. Images: A) don't allow us to copy-&-paste the code/errors/data for testing; B) don't permit searching based on the code/error/data contents; and [many more reasons](//meta.stackoverflow.com/a/285557). Images should only be used, in addition to text in code format, if having the image adds something significant that is not conveyed by just the text code/error/data. – Adrian Mole Sep 13 '21 at 14:32

ΩmegaMan · Answer 9 · 2021-05-20T16:15:06.943

Add a column to the query which can sub identify the data to sort on that.

In the below example I use a Common Table Expression with the selects what you showed and places them into specific groups on the CTE; then do a union off of both of those groups into AllStudents.

The final select will then sort AllStudents by the SortIndex column first and then by the name such as:

WITH Juveniles as
(
      Select 1 as [SortIndex], id,name,age From Student
      Where age < 15
),

AStudents as
(
      Select 2 as [SortIndex], id,name,age From Student
      Where Name like "%a%" 
),

AllStudents as
(
      select * from Juveniles
      union 
      select * from AStudents
)

select * from AllStudents
sort by [SortIndex], name;

To summarize, it will get all the students which will be sorted by group first, and subsorted by the name within the group after that.

score 0 · Answer 10 · edited Oct 22 '20 at 05:04

0

To apply an ORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

edited Oct 22 '20 at 05:04

Koushik Roy

6,868
2
12
33

answered Oct 21 '20 at 15:22

Daniel Ion

9
1

Works with PostgreSQL – 1_bug Apr 23 '21 at 07:30

score -1 · Answer 11 · answered Nov 25 '18 at 19:33

-1

Can use this:

Select id,name,age
From Student
Where age < 15
Union ALL
SELECT * FROM (Select id,name,age
From Student
Where Name like "%a%")

answered Nov 25 '18 at 19:33

Ahmad Aghazadeh

16,571
12
101
98

Fandango68 · Answer 12 · 2021-02-24T04:38:06.920

-1

Why not use TOP X?

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

The TOP 2000000 is an arbitrary number, that is big enough to capture all of the data. Adjust as per your requirements.

edited Feb 24 '21 at 04:38

answered Jan 15 '21 at 03:57

Fandango68

4,461
4
39
74

"top 100 percent" is better. – Larry Jun 11 '21 at 13:53
There are issues with "top 100 percent". See above links – Fandango68 Jun 15 '21 at 05:22
Interresting. Where are the links? – Larry Jun 15 '21 at 13:49
Hmm someone removed their comment it seems. Ok here: https://stackoverflow.com/questions/1393508/sql-massive-performance-difference-using-select-top-x-even-when-x-is-much-higher and here https://www.sqlshack.com/poor-database-indexing-sql-query-performance-killer-recommendations/ It's all to do with performance, depending on indexes. – Fandango68 Jun 21 '21 at 02:14

How to order by with union in SQL?

12 Answers12

Linked

Related