title: MySQL Select Last 10 Rows Using WHERE AVG()
body: <p>I am trying to pull a list of last 10 rows whose average rating is above 4. Right now this is done by selecting from the first table and then checking the average if its above 4 then it gets added to a variable if not it does nothing. This is a horrible way to do this especially if there are over 1000 rows.</p>
  
  <p>Here are the two queries:</p>
  
  <pre><code>SELECT * FROM `CLMS_reviews` WHERE id = 465 ORDER BY date DESC
  
  SELECT 
  rc.name, rr.rating
  FROM `CLMS_reviews_ratings` rr  
  LEFT JOIN `CLMS_reviews_categories` rc ON rc.cat_id = rr.cat_id  AND rc.website_id = 465
  WHERE rr.review_id = " . $row['id'] . "
  GROUP BY rc.name
  </code></pre>
  
  <p>How can I combine this into one query? I tried this but get an invalid use of GROUP BY:</p>
  
  <pre><code>SELECT 
  rr.review_id
  FROM `CLMS_reviews` r
  LEFT JOIN `CLMS_reviews_ratings` rr ON rr.review_id = r.id
  WHERE r.website_id = 465 AND AVG(rr.rating) &gt; 4
  GROUP BY rr.review_id LIMIT 10
  </code></pre>
  
  <p>SOLUTION From below:</p>
  
  <pre><code>SELECT review_id FROM `CLMS_reviews_ratings`
  WHERE review_id IN ((SELECT id FROM `CLMS_reviews` WHERE website_id = 465))
  GROUP BY review_id
  HAVING avg(rating) &gt; 4
  LIMIT 10
  </code></pre>