title: RANK OVER function in Hive
body: <p>I'm trying to run this query in Hive to return only the top 10 url which appear more often in the adimpression table.</p>
  
  <pre><code>select
          ranked_mytable.url,
          ranked_mytable.cnt
  
  from
          ( select iq.url, iq.cnt, rank() over (partition by iq.url order by iq.cnt desc) rnk
          from
                  ( select url, count(*) cnt
                  from store.adimpression ai
                          inner join zuppa.adgroupcreativesubscription agcs
                                  on agcs.id = ai.adgroupcreativesubscriptionid
                          inner join zuppa.adgroup ag
                                  on ag.id = agcs.adgroupid
                  where ai.datehour &gt;= '2014-05-15 00:00:00'
                          and ag.siteid = 1240
                  group by url
                  ) iq
          ) ranked_mytable
  
  where
        ranked_mytable.rnk &lt;= 10
  
  order by
          ranked_mytable.url,
          ranked_mytable.rnk desc
  
  ;
  </code></pre>
  
  <p>Unfortunately I get an error message stating:</p>
  
  <pre><code>FAILED: SemanticException [Error 10002]: Line 26:23 Invalid column reference 'rnk'
  </code></pre>
  
  <p>I've tried to debug it and until the <code>ranked_mytable</code> sub-queries everything goes smooth. I've tried to comment the <code>where ranked_mytable.rnk &lt;= 10</code> clause but the error message keep appearing.</p>