Top N Groups of Something [was Re: Explain THIS, please!]

In article <1992Dec23.150326.560_at_hhcs.gov.au>, geemic_at_hhcs.gov.au wrote:
>
> In article <1992Dec22.163411.557_at_hhcs.gov.au>, champs_at_hhcs.gov.au writes:
> > In article <1gtd91INN7ln_at_ef2007.efhd.ford.com>, wwm_at_ef5003.efhd.ford.com (Bill Meahan) writes:
 [stuff omitted]
> > It looks to me like you're trying to retreive the 10 parts with the highest
> > sum(cost). As far as I can tell it's impossible to retrieve the top 10 groups
> > of anything in a single query. You can retrieve the top ten of something, but
> > not the top 10 GROUPS of something. Your only chance is to use PL/SQL cursors

I think it can be done without PL/SQL, if you are willing to use views (and why not).

 create view part_count1 as
 select part, count(*) count
   from part_usage_table
  group by part;

 create view part_count as
 select a.part, a.count, count(*) rank
   from part_count1 a, part_count1 b
  where b.count > a.count or

        ( b.count = a.count and 
          b.part <= a.part )

 select part, count from part_count where rank <=10 order by rank;

I tested this with a slightly different table of my own and it worked, so unless I've made a typo, the final select, above, should give the top ten most frequently used parts (incidentally, in order of usage).

Note that an arbitrary decision is made: If two parts have the same frequency of use, then the one with the lower number is ranked earlier.

I make no claims that this is efficient. A PL/SQL program might be able to do it faster.

I originally learned this technique from Martin Jensen at dde in Denmark. Received on Mon Dec 28 1992 - 18:15:32 CET