Top values in aggregate queries

[Ted Allen]

Hi David,

You should be able to do this by using a subquery to
calculate the group rank of each record, then set the
criteria for that field (such as <4 for the top three.

The following subquery placed as a calculated field in
your main query should calculate the group rank that you
are looking for (of course you would have to substitute
the actual table name):

GroupRank: (SELECT Count(*) FROM tblCompanyProfits AS VT
WHERE VT.Region = tblCompanyProfits.Region AND
VT.Profits > tblCompanyProfits.Profits)+1

The full sql would look like the following (again you
would have to substitute the table name).

SELECT tblCompanyProfits.Region,
tblCompanyProfits.Company, tblCompanyProfits.Profits,
(SELECT Count(*) FROM tblCompanyProfits AS VT WHERE
VT.Region = tblCompanyProfits.Region AND VT.Profits >
tblCompanyProfits.Profits)+1 AS GroupRank
FROM tblCompanyProfits
WHERE ((((SELECT Count(*) FROM tblCompanyProfits AS VT
WHERE VT.Region = tblCompanyProfits.Region AND
VT.Profits > tblCompanyProfits.Profits)+1)<4))
ORDER BY tblCompanyProfits.Region,
tblCompanyProfits.Profits DESC;

Note that if you enter the criteria < 4 for this field,
and there are multiple companies at rank three, it will
list all of them, so you could get a listing of more than
three companies per group. If you want to have a hard
limit of three, the subquery could be modified to use the
company name as a tiebreaker in those cases so that
companies with the same profit will receive different
ranks based on their company name. To do this, the
subquery criteria would be changed to count all less than
the current records profit OR all with the same profit
with a lesser company name. The first part would be the
same as the above, but the OR condition would have to be
added.

Post back if you do want to use the company name as the
tiebreaker and you need help with the syntax.

HTH, Ted Allen

-----Original Message-----
I suspect there's no way to do this, but if anybody has an idea, I'd be much obliged.

I'm trying to get the top n (10, 5, 5%, whatever)

results from each group in an aggregate query (not just
the top n in the whole query). At the moment, the only
way I can figure out how to do it is to export to Excel,
number each row, and then paste back into Access (2002)
and restrict the query to ranks under n.

In fact, ideally I'd like to rank each group in a query

by row, starting with 1 at the top of each group, but
I'll settle for just being able to work with the top n of
each group. Example below if it helps.

Paste the following as a table, with the first line as field names:
Region Company Profits
US A 50
Europe V 15
US B 40
Europe W 14
US C 30
Europe X 13
US D 20
Europe Y 12
US E 10
Europe Z 10

I want a query that shows the three largest companies by profits in each region, i.e.:

Region Company Profits
Europe V 15
Europe W 14
Europe X 13
US A 55
US B 45
US C 35

Note that in real life I have too many Regions to do

each as a subquery without going mad.

As I say, I suspect I'm doomed, but any input would be

much appreciated! Thanks in advance!

 

[Guest]

Whoof... devilishly clever. The OR thing makes perfect sense, so if I need it, I should be fine. The real trick is making that dupe table and doing the offset counting business.

I now realize that pretty much the same solution to somebody else's problem was posted yesterday by Allen Browne but I didn't realize it also applied to me -- shouldn't this be in a FAQ somewhere? It seems pretty handy, if perhaps a bit too logically daunting for a lot of users to get their head around (I still need a few mintues myself...)

Thanks again,

Dave

 

[Ted Allen]

My pleasure Dave, glad it helped.

Yeah, subqueries do come up often, but like you said
sometimes it is hard to relate the previous posts to your
application until you use them a few times. This
probably would be a good FAQ topic, but then again there
are so many fine variations of how they can be applied it
might be difficult to cover in general terms.

These do open up a lot of possibilities once you get used
to them though. The correlated subqueries, such as
yours, are even a little trickier (than a standard
subquery) in the beginning because of the syntax required
to alias the second instance of the table and to relate
that table to the first instance of the table. But, they
get easy once you do a few.

By the way, I used VT as the alias for the second
instance of the table. This is often used as the alias
in subqueries (stands for Virtual Table), but of course
any alias name would work.

-Ted

-----Original Message-----
Whoof... devilishly clever. The OR thing makes perfect

sense, so if I need it, I should be fine. The real trick
is making that dupe table and doing the offset counting
business.

I now realize that pretty much the same solution to

somebody else's problem was posted yesterday by Allen
Browne but I didn't realize it also applied to me --
shouldn't this be in a FAQ somewhere? It seems pretty
handy, if perhaps a bit too logically daunting for a lot
of users to get their head around (I still need a few
mintues myself...)