Top 10 on multiple rows

[Sean]

I have the following structure.

SIC SalesCode YTD

My database has 200k rows with multiple sics and
SalesCodes.

I would like to structure a query that will go through the
entire database and return the Top Nvalue SalesCodes based
on YTD for every change in SIC.

Any ideas?

Thanks in advance.

 

[Michel Walsh]

Hi,


SELECT a.SIC, LAST(a.SalesCode), a.YTD
FROM myTable As a INNER JOIN myTable As b
ON a.SIC=b.SIC
AND b.YTD>= a.YTD
GROUP BY a.SIC, a.YTD
HAVING COUNT(*) <= 10



is a possible solution. Basically, it counts the number of records with a
YTD greater or equal to a.YTD, for a given a.SIC; and keeps those with a
count of 10, or less.


May take some time to run.



Hoping it may help,
Vanderghast, Access MVP