title: For each row in query select top 20 from other query
body: <p>I'm trying to do something and I'm not sure how to do it.</p>
  
  <p>I have some data like this:</p>
  
  <pre><code>WITH a AS (SELECT   theid, thename, thetimestamp FROM mytable)
  SELECT   thename, TRUNC (thetimestamp, 'HH24'), COUNT (theid) FROM   a
  group by thename,trunc(thetimestamp,'HH24') ORDER BY COUNT (theid) desc)
  </code></pre>
  
  <p>which returns me the count grouped by the hour and the name.</p>
  
  <p>I would like it to just be </p>
  
  <pre><code>for each hour, top X counts
  </code></pre>
  
  <p>Is that possible?</p>
  
  <hr>
  
  <p>I ended with:</p>
  
  <pre><code>SELECT thename, hour, cnt
  FROM
  ( SELECT thename, hour, cnt,
           rank() over (partition by hours order by cnt desc) rnk
    FROM
    ( SELECT   thename, TRUNC (thetimestamp, 'HH24') hour, COUNT (theid) cnt 
      FROM mytable
      group by thename,trunc(thetimestamp,'HH24')
    )
  )
  WHERE rnk &lt;= :X
  </code></pre>