RE: Top values in aggregate queries

From: David Skalinder (Skalinder_at_discussions.microsoft.com)
Date: 07/02/04 

Date: Fri, 2 Jul 2004 02:58:01 -0700

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" wrote:

> 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!
> >
> >Dave
> >.
> >
>

Relevant Pages

  • Top values in aggregate queries
    ... You should be able to do this by using a subquery to ... your main query should calculate the group rank that you ... Note that if you enter the criteria < 4 for this field, ...
    (microsoft.public.access.queries)
  • Re: Rank records in a query - crashes Access?
    ... I just tried removing the semicolon from the subquery, ... main query and the sub query were based on that, ... update the rank, required the data to be in the proper order. ...
    (microsoft.public.access.queries)
  • Top n Percent in subquery and EXISTS reserved keyword error
    ... I have achieved the query I want by adding a rank coulmn in Excel, ... Without having added the sum rank and gage rank fields, ... I have attempted the following subquery, with the dreaded error 3306 (EXISTS ...
    (microsoft.public.access.queries)
  • RE: Top values in aggregate queries
    ... subquery) in the beginning because of the syntax required ... I used VT as the alias for the second ... >> calculate the group rank of each record, ... >> results from each group in an aggregate query (not ...
    (microsoft.public.access.queries)
  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... subquery, but it's curious... ... repeated trips through the Query Editor's "graphical" side did ... The Jet Expression Evaluator (what functions & stuff are actually ... within brackets within the query. ...
    (microsoft.public.access.queries)