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)