Union order by problems

Question

The code below works fine up till the point i try to order it by name.

first time working with union so little confused on how to order these tables together when the first column names are different.

SELECT 
    e.emp_name 'Name',
    e.emp_gen 'Gender',
    ('Employee') 'Role'
FROM employee e
ORDER BY e.emp_name ASC
        UNION ALL
SELECT 
    s.sup_name 'Name',
    s.gen 'Gender',
    ('Supervisor') 'Role'
FROM supervisor s
ORDER BY s.sup_name ASC;

EDIT

have now tried this but doesn't seem to work as well

SELECT 
    e.emp_name 'Name',
    e.emp_gen 'Gender',
    ('Employee') 'Role'
FROM employee e
        UNION ALL
SELECT 
    s.sup_name 'Name',
    s.gen 'Gender',
    ('Supervisor') 'Role'
FROM supervisor s
ORDER BY s.sup_name ASC;

possible duplicate of [Using union and order by clause in mysql](http://stackoverflow.com/questions/3531251/using-union-and-order-by-clause-in-mysql) — Robby Cornelissen, Jun 08 '15 at 04:11
You need to `ORDER` the entire result set _after_ the `UNION`, q.v. Robby Cornelissen's link. — Tim Biegeleisen, Jun 08 '15 at 04:13
`UNION` is almost always a bad idea IMHO. You'd be much better off making a common person table (use "employee", one would assume that supervisors are still employees) and setting a field to define the role. — Cfreak, Jun 08 '15 at 04:14
the teacher has required us to use a union so we understand how they work — terrible programming, Jun 08 '15 at 04:29

score 0 · Answer 1 · answered Jun 08 '15 at 05:04

Figured it out.

everything was working fine like i said up till the 'order by', i think because of the fact that the name columns for the two tables were different so i changed the order by statement to reference the column.

i forgot you could do this.

also i did not mean to have union 'all' this was something i was just playing around with while stuck.

sorry if i confused people with that union all

SELECT 
    e.emp_name 'Name',
    e.emp_gen 'Gender',
    ('Employee') 'Role'
FROM employee e
        UNION
SELECT 
    s.sup_name 'Name',
    s.gen 'Gender',
    ('Supervisor') 'Role'
FROM supervisor s
ORDER BY 1 ASC;

ORDER BY 'Name' ASC is better. (Order by ordinal position is deprecated.) Why ( )s around 'Employee' and 'Supervisor'? Why UNION instead of UNION ALL? — jarlh, Jun 08 '15 at 05:29

score 0 · Answer 2 · answered Jun 08 '15 at 05:53

It should be possible to order by name itself like below.

SELECT EMPNAME AS COL1, DOB FROM #TABLE1
UNION
SELECT SUPNAME AS COL1, DOB FROM #TABLE2
ORDER BY COL1

Or

SELECT EMPNAME 'COL1', DOB FROM #TABLE1
UNION
SELECT SUPNAME 'COL1', DOB FROM #TABLE2
ORDER BY COL1

Union order by problems

2 Answers2