How to get max value of union query?

Question

My database schema is as follows

Department (dept_id, dept_name)

Student (student_id, student_name, major, level, age)

Professor (prof_id, prof_name, dept_id)

Class (name, meets_at, room, prof_id)

Enrolled (student_id, class_name, semester)

I want to find the age of the oldest student who is either a “History” major or enrolled in a course taught by “Michael Miller”.

My attempt was:

SELECT MAX(s.age)
FROM  professor as p 
join class as c  on p.prof_id = c.prof_id 
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller' 
 union SELECT max(s.age) from student as s where s.major ='history' ;

The result is:

MAX(s.age)

NULL

20

How can I select the maximum of this query? I would like to get the value 20.

Any help is very much appreciated.

Answer 1

You could wrap your query inside a subquery and select a max from that subquery like:

SELECT MAX(age) FROM
(
SELECT s.age
FROM  professor as p 
join class as c  on p.prof_id = c.prof_id 
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller' 
 union SELECT s.age from student as s where s.major ='history'
) as subquery;

or rely on a sorting and a limit like this

SELECT age FROM
(
SELECT s.age
FROM  professor as p 
join class as c  on p.prof_id = c.prof_id 
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller' 
 union SELECT s.age from student as s where s.major ='history'
) as subquery
ORDER BY age DESC
LIMIT 1;

But you could also select the maxes before unions like this:

SELECT MAX(age) FROM
(
SELECT max(s.age) AS age
FROM  professor as p 
join class as c  on p.prof_id = c.prof_id 
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller' 
 union SELECT max(s.age) AS age from student as s where s.major ='history'
) as subquery;

or

SELECT age FROM
(
SELECT MAX(s.age) AS age
FROM  professor as p 
join class as c  on p.prof_id = c.prof_id 
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller' 
 union SELECT MAX(s.age) AS age from student as s where s.major ='history'
) as subquery
ORDER BY age DESC
LIMIT 1;

Have a look at my SQLFiddle here

I created 3 approaches (using simplified table structures and sample data).

Using

CREATE TABLE Table1
    (`id` int, `age` int)
;

INSERT INTO Table1
    (`id`, `age`)
VALUES
    (1, 5),
    (2, 6),
    (3, 3)
;


CREATE TABLE Table2
    (`id` int, `age` int)
;

INSERT INTO Table2
    (`id`, `age`)
VALUES
    (1, 6),
    (2, 8),
    (3, 1)
;

and these 3 queries

SELECT MAX(age) FROM
(
  SELECT age FROM Table1
  UNION
  SELECT age FROM Table2
 ) as results

 ;
 SELECT age FROM
 (
   SELECT MAX(age) as age FROM Table1
   UNION
   SELECT MAX(age) as age FROM Table2
 ) AS results
 ORDER BY age DESC
 LIMIT 1
 ; 

 SELECT max(age) FROM
 (
   SELECT MAX(age) as age FROM Table1
   UNION
   SELECT MAX(age) as age FROM Table2
 ) AS results
 ;

All three return '8' but the third query looks like it's the most efficient one.
Test it on your table structures and see what you like best.