Using `with` to find the instructors with the highest salary

Question

I am learning SQL and was testing the with expression syntax when I ran into this error for my SQL query. I want to get the ids and names of all instructors from a university databse with the maximum salary. This database is from the book Database System Concepts by Silberschatz et al.

The schema of the instructor relation is instructor(ID, name, dept_name, salary) where the last attribute salary is a numeric type, MySQL Workbench is complaining that

Error Code: 1054. Unknown column 'max_salary.value' in 'where clause'

Here is my code

-- MySQL workbench complains with the above error!
with max_salary(value) as 
             (select max(salary) 
              from instructor)
select ID, name
from instructor
where max_salary.value = instructor.salary;

I know how to rewrite this without the with clause, as follows but I was having trouble with the syntax of with for this particular problem.

-- This works!
select ID, name
from instructor
where instructor.salary = (select max(salary) from 
                           instructor )

max_salary.value means look in the table max_salary for the attribute value. does a table max_salary exist? I doubt that — Bending Rodriguez
– Bending Rodriguez, Commented Oct 8 at 6:56
But hasn't max_salary been declared in the with clause? It does exist. — smilingbuddha
– smilingbuddha, Commented Oct 8 at 7:00
with max_salary as (select max(salary) as value from instructor) — Bending Rodriguez
– Bending Rodriguez, Commented Oct 8 at 7:02
max_salary exists, but isn't used - you need to select from it too. — jarlh
– jarlh, Commented Oct 8 at 8:33
You have not specified your WITH subquery as a row source in the FROM clause of the outer query. You must do this: with max_salary(value) as (select max(salary) from instructor) select ID, name from instructor JOIN max_salary ON max_salary.value = instructor.salary; — Akina
– Akina, Commented Oct 8 at 9:31

MatBailie · Accepted Answer · 2025-10-08 07:12:47Z

5

WITH creates a new rowset, it's kind of like a view.

Just like a view or table, you can only reference its row values if you reference it in a FROM clause.

with max_salary(value) as 
             (select max(salary) 
              from instructor)
select ID, name
from instructor
where instructor.salary = (SELECT value FROM max_salary);

Or...

with max_salary(value) as 
             (select max(salary) 
              from instructor)
select ID, name
from instructor
CROSS JOIN max_salary
where instructor.salary = max_salary.value;

answered Oct 8 at 7:12

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using `with` to find the instructors with the highest salary

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related