Top N Values

  • 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.
 
Sean said:
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.
Click to expand...

If the table has a single-field primary key field, say "SalesPK", then
something like this "air SQL" should work:

SELECT * FROM tblSales A
WHERE A.SalesPK In
(SELECT TOP 10 B.SalesPK FROM tblSales B
WHERE B.SIC=A.SIC
ORDER BY B.YTD DESC)

Here I've used 10 as "NValue". If this has to be a parameter (and you
can't just rewrite the SQL on the fly), or if there is no primary key on
the table, then you have to do something different. I'm not sure what
at the moment, so let's hope your situation meets these criteria.
Failing that, you may want to search Google Groups
(http://groups.google.com) in groups: *.*access.* for keywords "top N by
group".
 
Back
Top