Top 10 for each location

[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

 

[Michel Walsh]

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

 

[Michel Walsh]

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

 

[Dale Fye]

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