Get top 'n' records by report_id

Question

I have a simple view in my MSSQL database. It consists of the following fields:

report_id INT
ym VARCHAR -- YYYY-MM
keyword VARCHAR(MAX)
visits INT

I can easily get the top 10 keyword hits with the following query:

SELECT TOP 10 *
FROM top_keywords
WHERE ym BETWEEN '2010-05' AND '2010-05'
ORDER BY visits DESC

Now where it gets tricky is where I have to get the top 10 records for each report_id in the given date range (ym BETWEEN @start_date AND @end_date).

How would I go about getting the top 10 for each report_id? I've stumbled across suggestions involving the use of ROW_NUMBER() and RANK(), but have been vastly unsuccessful in their implementation.

Answer 1

Not tested (since I don't have a mssql install at home), but this should be close...

with IDs (ID) as (
  select
    report_id
  from
    top_keywords tk
  where
    tk.ym between @start_date and @end_date
    and row_number() over(partition by report_id order by visits desc) <= 10
)
  select
    top.*
  from
    top_keywords top
    join IDs on IDs.ID = top.report_id

Answer 2

Do you want to do a single query or a series of queries? For a series of queries, you can

• Step 1) Find all unique report IDs
• Step 2) Use query above for the top 10 for that report

Or do you really want a giant query that will give you all the results? How many results do you want, 10 per report id?

Answer 3

How about

SELECT *
FROM (SELECT *,
         ROW_NUMBER() OVER(PARTITION BY report_id ORDER BY (SELECT 0)) AS RN
         FROM top_keywords
         WHERE ym BETWEEN '2010-05' AND '2010-05') TK
WHERE RN <= 10