Further filter SQL results

Question

I've got a query that returns a proper result set, using SQL 2005. It is as follows:

select 
    case
    when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2'
    else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101)
    end as [Quarter],
    bugtypes.bugtypename,
    count(bug.bugid) as [Total]
from bug left outer join bugtypes on bug.crntbugtypeid = bugtypes.bugtypeid and bug.projectid = bugtypes.projectid
where 
    (bug.projectid = 44 
    and bug.currentowner in (-1000000031,-1000000045) 
    and bug.crntplatformid in (42,37,25,14))
or
    (bug.projectid = 44 
    and bug.currentowner in (select memberid from groupmembers where projectid = 44 and groupid in (87,88))
    and bug.crntplatformid in (42,37,25,14))

group by
    case
    when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2' else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101)
    end,
    bugtypes.bugtypename
order by 1,3 desc

It produces a nicely grouped list of years and quarters, an associated descriptor, and a count of incidents in descending count order. What I'd like to do is further filter this so it shows only the 10 most submitted incidents per quarter.

What I'm struggling with is how to take this result set and achieve that.

Answer 1

You are already ordering by Quarter and Total. Have you tried using:

SELECT TOP 10

..... rest of the query

EDIT: After reading your comment, I realize that you need to use RANK and Partition to make this work. You can wrap in a CTE as shown below:

;WITH IncidentsTable AS
(
   ... Insert Your Query here ...
)
SELECT * FROM
(
    SELECT [Quarter],
       BugTypeName,
       Total,
       Rank() OVER (Partition BY [Quarter] order by Total DESC) AS Ranking
     FROM
    IncidentsTable
)
WHERE
    Ranking <= 10
ORDER BY
      Quarter, Total;

Answer 2

A pattern like this would do it (Partition and number each quarter ignoring numbers <= 10):

SELECT * FROM (
    SELECT Qtr, fld,
    ROW_NUMBER() OVER(PARTITION BY Qtr ORDER BY fld) as RN 
    FROM tbl 
) AS T
WHERE RN <= 10
ORDER BY Qtr

NTILE(10) would probably do it also.

Answer 3

you could use rank and partition

Select * From 
(
    Select *,   Rank() over (Partition BY qtr order by qtr, id ) as Rank
    From
    (
              Select 1 as id, 1 as qtr,'hello' as msg
    union all select 2, 1,'hello'
    union all select 3,1,'hello'
    union all select 4,1,'hello'
    union all select 5,1,'hello'
    union all select 6,2,'hello'
    union all select 7,2,'hello'
    union all select 8,2,'hello'
    union all select 9,2,'hello'
    union all select 10,2,'hello'
    ) BaseQuery 
)QryWithRank
where rank <= 2