1

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.