-2

My current solution:

SELECT DEPT_NAME,SALARY
FROM EMPLOYEE 
WHERE (DEPT_NAME,SALARY) IN (SELECT DEPT_NAME,MAX(SALARY) AS MAXIMUM_SALARY
                             FROM EMPLOYEE
                             GROUP BY DEPT_NAME)

I don't know why this query worked in PostgreSQL but not in SQL-Server. It's throwing the following error:

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

Can anyone help me with this?

This is a subquery which returns multiple rows and columns.

After that I used the join condition which gave me the results but I want to know why the above query using is not working in SQL Server.

lemon
  • 14,875
  • 6
  • 18
  • 38
PranaV
  • 1
  • There's two conditions in your where clause. Also, what exactly are you trying to accomplish? Why not use the sub query only? – Jatin Morar May 29 '23 at 13:43
  • 1
    You can't use 2 columns in an in clause use an inner join ndtead – nbk May 29 '23 at 13:48
  • 1
    The tuples match is a PostgreSQL feature, which SQL Server did not adopt, simple as it looks like. – lemon May 29 '23 at 13:48
  • 1
    Also your query may be simplified a lot (and avoid the join operation altogether). If that could be helpful to you, consider updating your post with sample input data and expected output. – lemon May 29 '23 at 13:51
  • *I want to know why the above query using is not working in SQL Server* Because SQL Server is a different RDBMS, it does not support exactly the same syntax, each RDBMS has its own variations and additions to the SQL language. You cannot cut and paste code between platforms if it uses any features specific to one platform. – Stu May 29 '23 at 13:54

1 Answers1

1

As @lemon pointed out to you in comments, SQL Server doesn't support tuple comparison the way PostgreSQL and other RDBMS do. Check out this question and this answer for more information.

If I am understanding your goal right and this is to retrieve the employees records of those who have the highest salary within their departments, I think a nested query is in order:

SELECT e.dept_name, e.salary
FROM employee e
JOIN (
  SELECT dept_name, MAX(salary) AS maximum_salary
  FROM employee
  GROUP BY dept_name
) dept_maximum_salary
ON e.dept_name = dept_maximum_salary.dept_name
AND e.salary = dept_maximum_salary.maximum_salary;
alejandroMAD
  • 251
  • 2
  • 13