Get the top 'n' records of 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 first 10 keywords with the following query:

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

Now that it's getting complicated, I need to get the top 10 records for each report_idin a given date range ( ym BETWEEN @start_date AND @end_date).

How can I get the top 10 for each report_id? I came across suggestions related to using ROW_NUMBER () and RANK (), but was significantly unsuccessful in their implementation.

Answer 1

What 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

Answer 2

Not tested (since I don't have a mssql home installation), 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 3

?

• 1)
• Step 2) Use the query above for the top 10 for this report

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