title: SQL - Getting MAX AVG
body: <p>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.</p>
  
  <p>I have two tables:</p>
  
  <pre><code>PROFESSOR (ID, NAME, DEPARTMENT_ID and SALARY) and
  DEPARTMENT (ID, NAME).
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But this query is bringing me all the departments with the average, not the maximum.</p>
  
  <p>If someone could please assist me and explain in a easy way I would really appreciate it. 
  Thanks!</p>