Top 10 on multiple rows

  • Thread starter Thread starter Sean
  • Start date Start date
S

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.
 
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
 
Back
Top