union and order by with two sorting requirements
853700Jun 16 2011 — edited Jun 17 2011Hi,
I have 2 queries. 1st one selects all students who are older than 40 and sort them by last name, first name. 2nd one selects all students who are younger than 40 and sort them by first name, last name. This is a made up simplied situation for my question.
Is it possible to union these 2 queries with 2 different sorting requirements?
I know I can't do the following since sql requires the 'order by' to be at the end of union.
select .... from students where age >= 40 order by lastname , firstname
union
select .... from students where age < 40 order by firstname, lastname
Is there any way to accomplish what I intend to do?
Thanks for any help in advance.