Microsoft SQL Server Error: "An expression of non-boolean type specified in a context where a condition is expected, near ','."

Question

I'm trying to execute this query to find the employees with highest salary based on department.

Inner query works but I'm getting error from my outer query. I'd highly appreciate it if someone can point me in the right direction. Thank you.

Error Message: "An expression of non-boolean type specified in a context where a condition is expected, near ','."

select *
from employee e
where (dept_name, salary) in (select dept_name,max(salary)as HighestSalary
                  from employee e
                  group by dept_name)

I'd highly appreciate it if anyone can kindly point me in the right direction. Thank you!

Answer 1

Using standard sql you can do it this way:

select e.*
from (
  select dept_name, max(salary) as HighestSalary
  from employee
  group by dept_name
  ) m
  join employee e
    on m.dept_name = e.dept_name
   and m.HighestSalary = e.salary