Top n from each group

[MatthewPeters]

Hello All,

I've got a query that returns a set of customers grouped by location and then ordered by SalesYTD desc. I need the query to return only the Top 20 customers from each group/location.

I've developed a solution involving a cursor to accomplish this task. It works, but I don't like it and would prefer not to use it. I'm confident this can be done without a cursor but can't seem to figure it out. Any help would be appreciated. The query follows. Matt


Select pcNum, pcDesc,
cmNum, custName,
sum(salesMTD) as salesYTD, sum(costMTD) as salesYTD
From #CustSales
where cmNum not like 'CASH%' and cmnum not like 'ZZZZ%'
group by pcNum, pcDesc, cmNum, custName
Order By pcNum, salesYTD desc

 

[TomKane]

You can to "SELECT TOP 20 * FROM TABLE"

Is that the sort of thing you mean?

 

[MatthewPeters]

Yes, Top 20 will need to be implemented in the solution but "SELECT TOP 20 * FROM TABLE" will only give me 20 rows.

I need 20 rows from each group.

Matt

 

[MatthewPeters]

Well I found a solution that doesn't require a cursor. I found the answer on

http://www.sqlTeam.com.

The only problem is that this query is very slow. It's actually slower than using the cursor. I'm sure there's gotta be a way to speed this thing up but I've run out of time to mess with it. So, if nobody has any other suggestions I think I'll just use the cursor. The slow modified query follows. Matt

Select a.pcNum, a.pcDesc,
a.cmNum, a.custName,
sum(a.salesMTD) as salesYTD,
sum(a.costMTD) as costYTD
From #CustSales a
inner join #CustSales b on a.pcNum = b.pcNum and a.salesMTD <= b.salesMTD
group by a.pcNum, a.pcDesc, a.cmNum, a.custName
having count(distinct b.salesMTD) <= 20
Order By a.pcNum, salesYTD desc

 

[SQLSister]

So what indexes do you have? ANd have you defragmented them recently?

 

[MatthewPeters]

SqlSister, I have the following three simple indexes applied to the table . . .

pcNum_ind
cmNum_ind
pcNum_cmNum_ind

Would I need to defrag the indexes considering that I'm using a temp table? Thanks Matt