SQL - Getting MAX AVG

Question

So I have been trying to solve this for a while, and even though I have found many interesting things here I simply could not solve it the way it has been requested.

I have two tables:

PROFESSOR (ID, NAME, DEPARTMENT_ID and SALARY) and
DEPARTMENT (ID, NAME).

I have to write a query that shows the DEPARTMENT NAME that has the HIGHEST average SALARY. Also if more than one department have the highest average SALARY, should list all of then in any order.

I have tried so many things and in the end I created a monster, I think. i tried using HAVING but it did not work the way I did. I'm lost. The problem is that I need to use to aggregate functions.

SELECT b.nam, b.average
FROM ( SELECT DEPARTMENT.NAME AS nam, AVG(PROFESSOR.SALARY) AS average
   FROM PROFESSOR JOIN DEPARTMENT ON (PROFESSOR.DEPARTMENT_ID = 
DEPARTMENT.ID)
   GROUP BY DEPARTMENT.NAME) AS b
GROUP BY b.nam, b.average
ORDER BY b.average DESC

But this query is bringing me all the departments with the average, not the maximum.

If someone could please assist me and explain in a easy way I would really appreciate it. Thanks!

Answer 1

You can use this. If more than one row has same max avg value, with using WITH TIES you can bring all of them.

SELECT TOP 1 WITH TIES DEPARTMENT.NAME AS nam, AVG(PROFESSOR.SALARY) AS average
FROM PROFESSOR 
JOIN DEPARTMENT ON (PROFESSOR.DEPARTMENT_ID = DEPARTMENT.ID)
GROUP BY DEPARTMENT.NAME
ORDER BY AVG(PROFESSOR.SALARY) DESC

Answer 2

;WITH x AS (

        SELECT t.dept,
               T.avg_sal,
               rank() OVER(ORDER BY t.avg_sal DESC) AS rnk

        FROM
           (
                SELECT d.name AS 'dept',
                       avg(p.salary) AS avg_sal
                FROM department AS d
                     INNER JOIN
                     professor AS p ON p.department_id=d.id
                GROUP BY d.name
            ) AS t
)

-- all depts with highest avg sal
SELECT dept, avg_sal
FROM x
WHERE rnk = 1