title: Cassandra use aggregate function and then order by that aggregate
body: <p>I have a cassandra database with a table that has the following columns: </p>
  
  <ul>
  <li>itemid</li>
  <li>userid </li>
  <li>rating</li>
  </ul>
  
  <p><code>itemid</code> and <code>userid</code> are the primary key. My query looks like this:</p>
  
  <pre><code>SELECT itemid, avg(rating) as avgRating from mytable GROUP BY itemid order by avgRating asc;
  </code></pre>
  
  <p>I get the following error:</p>
  
  <blockquote>
    <p>InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."</p>
  </blockquote>
  
  <p>How can I fix this?</p>
  
  <p>I need to order by the average ratings after so I can get the top 10 movies based on their average rating.</p>