pulling top 20 records from "multi-tier" table

[Hosacans]

HI all,

I receive a raw file each day that reports on sales performance to ALL products in the whole business organization. The file is drilled down to item level and the hierarchy of how the data is categorized is by Store>Product Dept>item id.

i want to pull the top 20 performing items by each dept by each store. but so far i've thought of creating a query for each store/dept... but that would be alot of queries. top 20 items are determined by sales.

Also, each store has different number of depts, and each dept has different number of items, some does not even have 20 items for sale.

Please advise

Thanks
Hosackies

 

[Remou]

Perhaps:
[tt]SELECT T1.Group, T1.Sales
FROM Table2 AS T1
WHERE (((T1.ID) In (SELECT TOP 20 T2.ID FROM Table2 T2 Where T2.Group=T1.Group ORDER BY T2.Sales))
ORDER BY T1.Group;[/tt]

 

[Hosacans]

this works great remou thanks so much...

however, does this sql only filter "one level" of data? when i inserted the fields.. it only return records by product_dept and item..

how does the sysntax look it, if i want the records to be filtered by store/dept/item?

Thanks
hosackies

 

[Remou]

As in (?):
[tt]SELECT T1.Store, T1.Dept, T1.Item, T1.Sales
FROM Table2 AS T1
WHERE (((T1.ID) In (SELECT TOP 20 T2.ID FROM Table2 T2 Where T2.Store=T1.Store And T2.Dept=T1.Dept And T2.Item=T1.Item ORDER BY T2.Sales))
ORDER BY T1.Store, T1.Dept, T1.Item, T1.Sales;[/tt]

 

[Hosacans]

Remou,

the sql returns the same number of records as to the orginal table. as if the select top 20 section had no affect...

any thoughts?

Thanks
Hosackies

 

[Remou]

Top twent for three 'levels' can be a lot of records. Change twenty to 2 to see if it is working the way you expect. Remember that a top value includes duplicates, if there are any.

 

[PHV]

And what is YOUR actual SQL code ?
Does your table have a primary key ?
My suggestion is a ranking query (I've posted a lot here ...) with an HAVING clause limiting the rank (<=20)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hosacans]

HI guys,

The code that Remou suggested was actually working. however, it had an addition "check" in the second table for t1.item = t2.item... so i think the query was matching item from t1 to t2 thus the same result set.

i simply removed that check and the sql is pulling the top 20 records by store/dept/item.. exactly the way i wanted.

Thanks so much for the help guys.
Hosackies

 

[Hosacans]

Hi All,

Follow up questions for this SQL. I've been using the SQL suggested by Remou and it's working great. However, the business is now requesting a different number of records pulled for different groups. Here's the SQL.

SELECT T1.Loc_No, T1.Div_No, T1.Style, Sum(T1.Sales_R_Mtd) AS TTL
FROM Main_Refill AS T1

WHERE (((T1.Style) In (SELECT TOP 20 T2.Style FROM Main_Refill T2  Where T2.Loc_No=T1.Loc_No And T2.Div_No=T1.Div_No  ORDER BY T2.Sales_R_Mtd Desc;)) AND ((T1.Sales_R_Mtd)<>0))

GROUP BY T1.Loc_No, T1.Div_No, T1.Style
ORDER BY T1.Loc_No, T1.Div_No, Sum(T1.Sales_R_Mtd) DESC;

it's there anyway to pull a variable from a field on the table onto this SQL to replace the "Top 20" to "Top [num]"? i can set the "#_of_records" as a column on the query table by different div_no...

i did a search and found some similar threads on this matter. most of them suggest to set parameters using VBA, which i'm also not sure on how to do..

any thoughts?

Much thanks
Hosackies