Skip to Content
0
Former Member
Jun 20, 2011 at 08:56 PM

Top n Items per Industry Group

27 Views

I'm working on a query that will pull the TOP(20) Items sold for each Industry for a period of time. This is the query that I've started with as a base which pulls all Items, grouped by Industry and then the total qty sold in desc order.

SELECT T0.[U_Industry], T2.[ItemCode], SUM(T2.[Quantity]) as QTY 
FROM OCRD T0  
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode 
INNER JOIN INV1 T2 ON T1.DocEntry = T2.DocEntry 
WHERE T1.[DocDate] >=[%0] and  T1.[DocDate] <=[%1] 
GROUP BY T0.[U_Industry], T2.[ItemCode] 
ORDER BY T0.[U_Industry], SUM(T2.[Quantity]) DESC

It seems that there are multiple ways to do this (RANK, ROW_NUMBERS,etc.), but I'm having a hard time getting it to work in SAPB1. Any help would be appreciated. Thanks,