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

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)

Hmmm.


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

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

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

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



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)

From the documentation:

What about:



create table inst_lname (varchar lname)

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


then



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


You can’t do that in Oracle or MySQL?

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

Zev Steinhardt

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.

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.

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.)

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

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.