MySQL: I retrieved a list of maximum salaries ordered by department. How to find the minimum of these maximum salaries?

I have a university database and from the instructor table, initially I was told to find the highest professor salary in each department and rename it as 'max_salary'. The instructor table has four columns, 'ID', 'name', 'dept_name', and 'salary'. I found the maximum salary for each department using the query below:

SELECT dept_name, MAX(salary) AS max_salary
FROM instructor
GROUP BY dept_name;

Then I was asked to find the lowest value of the group of salaries produced from the query above. I believe I may be close with this query:

SELECT min(salary)
    FROM 
        (SELECT dept_name, MAX(salary)
        FROM instructor
        GROUP BY dept_name) as lowest;

However, this gives me an error message stating "Unknown column 'salary' in 'field list'.

I can't seem to find any answers in textbooks or online about how to find the lowest value of a group of maximum values, and I was only given the cryptic hint "You have to use a nested query," which has not helped much.
Has anyone else heard of how to do something like this?

Solution

You were really close

SELECT min(max_sal) as lowest
FROM 
(
   SELECT dept_name, MAX(salary) as max_sal
   FROM instructor 
   GROUP BY dept_name
) as tmp

You need to give the calculated column an alias name. I used max_sal. Then you can refer to it in outside queries.

Another possibiliy would be to order the data and take only the first result:

SELECT dept_name, MAX(salary) as max_sal
FROM instructor 
GROUP BY dept_nam
ORDER BY MAX(salary) ASC
LIMIT 1