title: HiveSQLException: cannot recognize input near 'SELECT' 'MAX' '(' in expression specification
body: <p>I'm trying to get the maximum value of a count. The code is as follows </p>
  
  <pre><code>SELECT coachID, COUNT(coachID)
  FROM coaches_awards GROUP BY coachID
  HAVING COUNT(coachID) = 
  (
     SELECT MAX(t2.awards)
      FROM (
               SELECT coachID, count(coachID) as awards
               FROM coaches_awards
               GROUP BY coachID
            ) t2
  );
  </code></pre>
  
  <p>Yet something keeps failing. The inner query works and gives the answer that I want and the outer query will work if the inner query is replaced by the number required. So I'm assuming I've made some syntax error. </p>
  
  <p>Where am I going wrong?  </p>