title: Hive - top n records within a group
body: <p>I am currently using Hive and I have a table with the fields user_id and value. I want to order the values in descending order within each user_id and then only emit the top 100 records for each user_id. This is the code I am attempting to use:</p>
  
  <pre><code>DROP TABLE IF EXISTS mytable2
  CREATE TABLE mytable2 AS
  SELECT * FROM 
  (SELECT *, rank (user_id) as rank
  FROM 
  (SELECT * from mytable
  DISTRIBUTE BY user_id
  SORT BY user_id, value DESC)a )b
  WHERE rank&lt;101
  ORDER BY rank;
  </code></pre>
  
  <p>However when I run this query, I get the following error:</p>
  
  <pre><code>Error while compiling statement: FAILED: SemanticException [Error 10247]:    Missing over clause for function : rank [ERROR_STATUS]
  </code></pre>
  
  <p>FYI - My UserIds are alpha-numeric.</p>
  
  <p>Can anyone help?</p>
  
  <p>Thanks in advance.</p>
  
  <p>Add comment</p>