Top 20 records

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

I have 10 firms and I want to get the top 20 sales for each
Firm ( Field is [firm_name]). I used this but it does not
pull the top sales. I want the qry to pull top highest
sales for each of the 10 firms. THANKS for any
suggestions!!

SELECT TOP 20 [qry 2003 Top 20_2].[C_NAME], [qry 2003 Top
20_2].[TRADE_OFFICE], [qry 2003 Top 20_2].[firm_id], [qry
2003 Top 20_2].[firm_name], [qry 2003 Top 20_2].[zip],
[qry 2003 Top 20_2].[terr], [qry 2003 Top 20_2].
[SumOfAnnuityFund], [qry 2003 Top 20_2].[SumOfMutualFund],
[SumOfAnnuityFund]+[SumOfMutualFund] AS Total
FROM [qry 2003 Top 20_2]
ORDER BY [SumOfAnnuityFund]+[SumOfMutualFund] DESC;
 
Hi,


SELECT whatever
FROM myTable As a
WHERE a.primaryKey IN(
SELECT TOP 10 b.primaryKey
FROM myTable As b
WHERE b.firm = a.firm
ORDER BY b.something DESC )
ORDER BY a.firm, a.something DESC



Note that if they is equality between two "something" in position 10, for a
given firm, you will get 11 records for that firm. If this is not what you
want, you can change the inner order by to:

ORDER BY b.something DESC, b.primaryKey


I assumed you have a primary key available, and the field name is
primaryKey.


The following query does not require a primary key field ( or may easily
work with a primary key made of more than one field):


SELECT a.firm, a.something
FROM myTable As a LEFT JOIN myTable As b
ON (a.firm = b.firm) AND (a.something < b.something)
GROUP BY a.firm, a.something
HAVING COUNT(*) < 10
ORDER BY a.firm, a.something DESC


it just compute the rank with a count on an inner join (well, an outer join
produces a rank starting at 0), and we just keep the records having a rank
between 0 and 9. Note that the subtlety is about a possible equality in 10th
position, for two records or more, for a given firm. Here, you have other
possibilities. Indeed, as example, to remove them all, change the LEFT join
to an INNER join ( for speed reason only), and change the < for <= (at 2
places).



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top