title: why is my query returning the right max value but not the corresponding name that goes with that average value?
body: <p>I have a query that is averaging the ratings of a restaurant (from a separate review table) and I'm trying to get the max value out of that. Here is what a query getting the average rating based on joining a restaurant to its reviews gets:</p>
  
  <pre><code>1   Panera  4.0000
  2   BurgerKing  4.5000
  3   ChuckECheese    2.6667
  4   Cafe Gratitude  2.0000
  </code></pre>
  
  <p>However, I'm trying to get just the restaurant with the highest review and its rating and I'm using this query:</p>
  
  <pre><code>select BusinessName, MAX(AvgReview) as Rating from
  (select BusinessName, avg(ReviewRating) as AvgReview
  from Business
  join Review 
  on Review.BusinessID = Business.BusinessID 
  group by Business.BusinessID) AvgRatingsTable;
  </code></pre>
  
  <p>But that is resulting in:</p>
  
  <pre><code>Panera  4.5000
  </code></pre>
  
  <p>Where it gets the maximum rating correctly but not the restaurant name. I've tried doing adding "where AvgRating = Max(AvgRating)" but it says invalid use of group function.</p>
  
  <p>Additionally, when I try to find the restaurant with the minimum review using:</p>
  
  <pre><code>select AvgRatingsTable.BusinessName, MIN(AvgRatingsTable.AvgReview) as Rating from
  (select BusinessName, avg(ReviewRating) as AvgReview
  from Business
  join Review 
  on Review.BusinessID = Business.BusinessID 
  group by Business.BusinessName) AvgRatingsTable;
  </code></pre>
  
  <p>I receive</p>
  
  <pre><code>BurgerKing  2.0000
  </code></pre>
  
  <p>So doing groupby BusinessName or BusinessID doesn't really help; it just grabs the first name.</p>
  
  <p>What am I doing wrong here? How do I tell it I want the name that corresponds to that maximum/minimum average?</p>
  
  <p>EDIT EDIT EDIT EDIT EDIT</p>
  
  <p>If I do this, with a groupby on the outer query</p>
  
  <pre><code>select AvgRatingsTable.BusinessName, MIN(AvgRatingsTable.AvgReview) as Rating from
  (select BusinessName, avg(ReviewRating) as AvgReview
  from Business
  join Review 
  on Review.BusinessID = Business.BusinessID 
  group by Business.BusinessName) AvgRatingsTable
  group by BusinessName;
  </code></pre>
  
  <p>It just returns all of the tables instead of at least giving me the minimum rating. Same if I do groupby BusinessName, AvgReview.</p>