For each row in query select top 20 from other query

Question

Hi.

I'm trying to do something and I'm not sure how to do it.

I have some data like this:

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)

which returns me the count grouped by the hour and the name.

I would like it to just be

for each hour, top X counts

Is that possible?

---

I ended with:

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 <= :X

Answer 1

Try:

SELECT thename, hour, cnt
FROM
( SELECT thename, hour, cnt,
         rank() over (partition by thename 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 <= :X

(I didn't see the purpose of the WITH clause so I removed it from mine).

Answer 2

You could do that with row_number(), but it requires another subquery or another CTE. Here's the double CTE, since Tony Adrews already posted the subquery approach:

WITH a AS (
    SELECT thename, TRUNC(thetimestamp, 'HH24') as hour, COUNT(*) cnt
    FROM  mytable
    GROUP BY thename, TRUNC(thetimestamp, 'HH24')
), b AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY hour ORDER BY ctn DESC) rn,
        thename, hour, cnt
    FROM a
)
SELECT *
FROM b
WHERE rn < 20