0

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!

mykeerat
  • 1
  • 1
  • 1
    Sql Sever doesn't support vector expressions in the IN predicate. Scalars only. You can use EXISTS instead of it for example. – Serg Dec 13 '22 at 08:37
  • I would point you towards the [Microsoft documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16) which explains the syntax and usage. – Stu Dec 13 '22 at 09:00

1 Answers1

0

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
James
  • 2,954
  • 2
  • 12
  • 25