# Chasing the SQuirreL:  UNION ALL not compatible with ORDER BY?

**URL:** <https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440>\
**Category:** Factual Questions\
**Created:** [February 11, 2009, 10:47pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440 "2009-02-11T22:47:03Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [February 11, 2009, 10:47pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/1 "2009-02-11T22:47:03Z")

</div>

This is a SQL question.

consider this SQL query:

select student.fname, student.lname, student.student\_id, concat (student.ssn, ’ ', student.sex) as ssn\_sex, course.course\_id, course.description, instructor.lname, instructor.fname from student, class, instructor, course where student.ssn = class.stu\_ssn and class.inst\_ssn = instructor.ssn and class.course\_id = course.course\_id and lower(instructor.lname) = ‘sultan’  
/\* order by student.lname, student.fname\*/  
union all  
select student.fname, student.lname, student.student\_id, concat (student.ssn, ’ ', student.sex) as ssn\_sex, course.course\_id, course.description, instructor.lname, instructor.fname from student, class, instructor, course where student.ssn = class.stu\_ssn and class.inst\_ssn = instructor.ssn and class.course\_id = course.course\_id and lower(instructor.lname) = ‘pefanis’  
/\* order by student.lname, student.fname\*/

That query runs and snags all the students who are in Dr. Sultan’s class and also all those who are in Dr. Pefanis’ class. Works fine.

But note the commented out sections. Those would sort (i.e., ‘order’) the data by lastname, firstname. And if either half of the query (the Dr. Sultan part or the Dr. Pefanis part) is run separately, i.e., NOT as a union query, the ‘order by’ portions can be uncommented so that they execute and all is well. But I can’t find a way to get the damn thing to sort the results of the query when it is a union query! I’ve tried only using the final order by (since the desired result is to sort the overall results anyhow, or doing it as shown (with separate sorts for each of the “unioned” bits): no go.

Both Oracle and MySQL are disgusted with me when I try it and error it back in my face.

(We have parallel environments with identical table structure in Oracle and MySQL to play with)

---

<div class="post-metadata">

**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\
**Post date:** [February 11, 2009, 10:51pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/2 "2009-02-11T22:51:50Z")

</div>

Hmmm.

```auto

create table #a (num int)
create table #b (num int)

insert #a values (1)
insert #a values (2)
insert #a values (3)
insert #a values (4)
insert #b values (3)
insert #b values (4)
insert #b values (5)
insert #b values (6)

select * from #a
union all
select * from #b
order by num

```

This works, but I tested it out in SQL Server.

I’m going to take a closer look at your query and see what the problem is.

Zev Steinhardt

---

<div class="post-metadata">

**Author:** ![Ponderoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ponderoid/32/19221_2.png) [@Ponderoid](https://boards.straightdope.com/u/Ponderoid)\
**Post date:** [February 11, 2009, 11:01pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/3 "2009-02-11T23:01:26Z")

</div>

You only need one “order by” clause, the final one. You might need to fiddle around with aliases for the table and field names to get something properly unique.

\*\*\* Ponder

---

<div class="post-metadata">

**Author:** ![Ludovic](https://avatars.discourse-cdn.com/v4/letter/l/7ab992/32.png) [@Ludovic](https://boards.straightdope.com/u/Ludovic)\
**Post date:** [February 11, 2009, 11:13pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/4 "2009-02-11T23:13:05Z")

</div>

You could probably do something like

Select a,b,c  
from  
(select a,b,c  
from table\_a  
where …  
order by a)  
union all  
select a,b,c  
from  
(select a,b,c  
from table\_a  
where …  
order by a)

to get both classes sequentially in order

---

<div class="post-metadata">

**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\
**Post date:** [February 11, 2009, 11:13pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/5 "2009-02-11T23:13:30Z")

</div>

I took the liberty of making your code a bit more readable. This script works.

```auto

create table #student 
	(	
		fname varchar(50),
		lname varchar(50),
		student_id varchar(50),
		ssn_sex varchar(50),
	)

create table #class 
	(
		course_id varchar(50),
		stu_ssn varchar(50),
		instr_ssn varchar(50)
	)

create table #instructor
	(
		inst_ssn varchar(50),
		lname varchar(50),
		fname varchar(50)
	)

create table #course
	(
		course_id varchar(50),
		course_description varchar(50),
	)

insert #student values ('Joe', 'Smith', '123', '000-00-0001_m')
insert #student values ('John', 'Doe', '456', '000-00-0002_m')
insert #student values ('Richard', 'Roe', '123', '000-00-0003_m')
insert #student values ('Al', 'Capone', '123', '000-00-0004_m')

insert #course values ('111', 'Basketweaving 101')
insert #course values ('112', 'Rocket Science 101')

insert #instructor values ('999-99-9999', 'Sultan', 'Al')
insert #instructor values ('999-99-0000', 'Pefanis', 'Bill')

insert #class values ('111', '000-00-0001_m', '999-99-9999')
insert #class values ('111', '000-00-0002_m', '999-99-9999')
insert #class values ('112', '000-00-0003_m', '999-99-0000')
insert #class values ('112', '000-00-0004_m', '999-99-0000')

		

SELECT	s.fname,
		s.lname,
		s.student_id,
		s.ssn_sex,
		co.course_id,
		co.course_description,
		i.lname,
		i.fname
from	#student s
			join	#class cl 
			on s.ssn_sex = cl.stu_ssn			
			join #instructor i
			on cl.instr_ssn = i.inst_ssn
			join #course co
			on cl.course_id = co.course_id
			and i.lname = 'sultan'
union all
SELECT	s.fname,
		s.lname,
		s.student_id,
		s.ssn_sex,
		co.course_id,
		co.course_description,
		i.lname,
		i.fname
from	#student s
			join	#class cl 
			on s.ssn_sex = cl.stu_ssn			
			join #instructor i
			on cl.instr_ssn = i.inst_ssn
			join #course co
			on cl.course_id = co.course_id
			and i.lname = 'Pefanis'
order by	s.lname,
			s.fname

```

Zev Steinhardt

(Note: Normally my code is a lot neater and more readable. This is the quick and dirty version)

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [February 11, 2009, 11:20pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/6 "2009-02-11T23:20:39Z")

</div>

From the [documentation](http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381):

> [@](#):
>
> Restrictions on the Set Operators The set operators are subject to the following restrictions:
> 
> …  
> \* You cannot specify the order\_by\_clause in the subquery of these operators.

What about:

```auto

create table inst_lname (varchar lname)

insert into inst_lname (lname) values ('sultan')
insert into inst_lname (lname) values ('pefanis')

```

then

```auto

select
  student.fname,
  student.lname,
  student.student_id,
  concat (student.ssn, ' ', student.sex) as ssn_sex,
  course.course_id,
  course.description,
  instructor.lname,
  instructor.fname
from student
join class on student.ssn = class.stu_ssn
join instructor on class.inst_ssn = instructor.ssn
join inst_lname on lower(instructor.lname) = inst_lname.lname
join course on class.course_id = course.course_id
order by student.fname, student.lname

```

---

<div class="post-metadata">

**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\
**Post date:** [February 11, 2009, 11:24pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/7 "2009-02-11T23:24:58Z")

</div>

You can’t do that in Oracle or MySQL?

Wow. I’m surprised. It works in SQL Server.

Zev Steinhardt

---

<div class="post-metadata">

**Author:** ![Punoqllads](https://avatars.discourse-cdn.com/v4/letter/p/d2c977/32.png) [@Punoqllads](https://boards.straightdope.com/u/Punoqllads)\
**Post date:** [February 11, 2009, 11:43pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/8 "2009-02-11T23:43:36Z")

</div>

I’m pretty sure you could wrap the whole thing up into a subquery and doing an ORDER BY in the main query. E.g.,

SELECT \* FROM  
( whole\_big\_query )  
ORDER BY student\_fname, student\_lname

Note that you’d have to give the student’s first and last name columns distinct labels from the intstructor’s first and last names inside of ‘whole\_big\_query’.

However, the point of my first post is that you can get rid of the union statement where all of the tables are identical by joining on an additional table containing the particular instructor names.

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [February 11, 2009, 11:44pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/9 "2009-02-11T23:44:56Z")

</div>

SQL doesn’t work that way, you can’t order within a UNION ALL.

Just do the ordering after the union. This should work:

select student.fname, student.lname, student.student\_id, concat (student.ssn, ’ ', student.sex) as ssn\_sex, course.course\_id, course.description, instructor.lname, instructor.fname from student, class, instructor, course where student.ssn = class.stu\_ssn and class.inst\_ssn = instructor.ssn and class.course\_id = course.course\_id and lower(instructor.lname) = ‘sultan’

union all

select student.fname, student.lname, student.student\_id, concat (student.ssn, ’ ', student.sex) as ssn\_sex, course.course\_id, course.description, instructor.lname, instructor.fname from student, class, instructor, course where student.ssn = class.stu\_ssn and class.inst\_ssn = instructor.ssn and class.course\_id = course.course\_id and lower(instructor.lname) = ‘pefanis’

order by instructor.lname, student.lname, student.fname

ETA: or what they said. That’s what I get for not reloading the window.

---

<div class="post-metadata">

**Author:** ![Ludovic](https://avatars.discourse-cdn.com/v4/letter/l/7ab992/32.png) [@Ludovic](https://boards.straightdope.com/u/Ludovic)\
**Post date:** [February 13, 2009, 5:01pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/10 "2009-02-13T17:01:24Z")

</div>

> [@Ludovic](#):
>
> You could probably do something like
> 
> Select a,b,c  
> from  
> (select a,b,c  
> from table\_a  
> where …  
> order by a)  
> union all  
> select a,b,c  
> from  
> (select a,b,c  
> from table\_a  
> where …  
> order by a)
> 
> to get both classes sequentially in order

Now that the question’s been answered I think I’ll throw in a question about my answer.

I tried the above sort of query, designed to get a list of the first class in order followed by a list of the second class in order, and it does work that way in Oracle.

But does the SQL documentation designate the retrival order of an inner query if it is not designated in the outer query? Or does it only offer random access since the outer query does not designate an order? (I know that for most SQL platforms this will work anyway since they want their platform to be well behaved but I just don’t know if the SQL design calls for it or not.)

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [February 13, 2009, 5:21pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/11 "2009-02-13T17:21:45Z")

</div>

In Oracle, you can only have one Order BY clause and it goes at the bottom. Different aliases in UNION queries can cause problems but this is easy to work around. Just use field place numbers. In this case, it is ORDER BY 2,1

---

<div class="post-metadata">

**Author:** ![borschevsky](https://avatars.discourse-cdn.com/v4/letter/b/97f17d/32.png) [@borschevsky](https://boards.straightdope.com/u/borschevsky)\
**Post date:** [February 13, 2009, 5:26pm UTC](https://boards.straightdope.com/t/chasing-the-squirrel-union-all-not-compatible-with-order-by/485440/12 "2009-02-13T17:26:55Z")

</div>

Have you tried using only one ORDER BY, at the end, but omitting the table from it? That is, just:

select student.lname as s\_lname, student.fname as s\_fname, …  
order by s\_lname, s\_fname

Edit: or field place numbers, as mentioned above.