Top 25 brokers query help

[Pat Dools]

Hello,

I posted this in the wrong section, and am reposting it to the 'Queries'
help section:

Hello,

I am trying to write a query that will show me the Top 25 Brokers (by
'TotalPremium') for each Broker Manager ('MMPM', in my case). I have the
following query, but it only supplies the Top 25 for the first MMPM
(alphabetically) in my list:

SELECT TOP 25 rpt_MNTHLY_YTD_PC_HIT_RATIO.MMPM,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_NAME,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_ADDRESS,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_CITY,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_STATE,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_NEW) AS SumOfPOL_NEW,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_PREMIUM_NEW) AS SumOfPOL_PREMIUM_NEW,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_RNWL) AS SumOfPOL_RNWL,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.POL_PREMIUM_RNWL) AS SumOfPOL_PREMIUM_RNWL,
Sum([POL_PREMIUM_NEW]+[POL_PREMIUM_RNWL]) AS TotalPremium,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.NOT_QUOTED_COUNT) AS SumOfNOT_QUOTED_COUNT,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.QT_COUNT) AS SumOfQT_COUNT,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.DECLINE_COUNT) AS SumOfDECLINE_COUNT,
Sum(rpt_MNTHLY_YTD_PC_HIT_RATIO.RNWL_QUOTE_COUNT) AS SumOfRNWL_QUOTE_COUNT
FROM rpt_MNTHLY_YTD_PC_HIT_RATIO
GROUP BY rpt_MNTHLY_YTD_PC_HIT_RATIO.MMPM,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_NAME,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_ADDRESS,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_CITY,
rpt_MNTHLY_YTD_PC_HIT_RATIO.BROKER_STATE
ORDER BY rpt_MNTHLY_YTD_PC_HIT_RATIO.MMPM,
Sum([POL_PREMIUM_NEW]+[POL_PREMIUM_RNWL]) DESC;

How can i get the Top 25 for EACH 'MMPM' to show in my results?

I also tried to do a Union query, where I just put the name of each MMPM in
the WHERE clause, but it complains about the
'Sum([POL_PREMIUM_NEW]+[POL_PREMIUM_RNWL])' piece of the ORDER BY clause, but
only for the second part of the Union query, which doesn't make sense to me,
either.

Thanks,

 

[Michel Walsh]

I will simplify the problem to finding the top 10 cities (population) per
country. At least two possible solutions, with Jet:


SELECT country, city
FROM mytable AS a
WHERE city IN( SELECT TOP 10 b.city
FROM myTable As b
WHERE a.country=b.country
ORDER BY b.population DESC)



or (a little bit faster, probably)



SELECT a.country, a.city
FROM myTable AS a INNER JOIN myTable As b
ON a.country = b.country
AND a.population<= b.population
GROUP BY a.country, a.city
HAVING COUNT(*) <= 10



For both query, you can add:

ORDER BY a.country, a.population DESC


but that is not strictly required for the good execution of the query.



Hoping it may help,
Vanderghast, Access MVP