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.