Top 10 for each location

  • Thread starter Thread starter Graham
  • Start date Start date
G

Graham

Hi all,

The problem is this: Displaying top 10 results for 200
locations???

I have a database which contains a table holding data on
telepones calls e.g. Telephone number, Location call made
from and Cost. Each telephone number is associated with a
different location (i.e a location has many telephone
calls) I need a query to run through the entire location
list displaying the top 10 calls for each location.

Any help - much appreciated!

Graham
 
Hi,



SELECT a.*
FROM tableName As a
WHERE a.pk IN ( SELECT TOP 100 b.pk
FROM tableName As b
WHERE a.loc = b.loc
ORDER BY b.volume DESC)

ORDER BY a.loc, a.volume DESC



Where I assumed pk is the primary key field name, loc is the field with the
group value (the location) , and volume is the field giving the order of the
TOP thing.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


Make the inner query TOP 10, not TOP 100, that is.


To be sure you have just 10, not more (ex-equo), change the ORDER BY
to:

ORDER BY b.volume DESC, b.pk )


Hoping it may help,
Vanderghast, Access MVP
 
Graham,

See my response to "Multiple top 10 listings" 10/9/03 4:07 PM

--
HTH

Dale Fye


Hi all,

The problem is this: Displaying top 10 results for 200
locations???

I have a database which contains a table holding data on
telepones calls e.g. Telephone number, Location call made
from and Cost. Each telephone number is associated with a
different location (i.e a location has many telephone
calls) I need a query to run through the entire location
list displaying the top 10 calls for each location.

Any help - much appreciated!

Graham
 
Back
Top