Re: Top ten?

From: David R. Thrash <dthrash_at_succeed.win.net>
Date: Wed, 21 Apr 1993 02:31:24 GMT
Message-ID: <31_at_succeed.win.net>

 

>Sorry if this is a FAQ.
>
>Suppose I am doing a group by query which returns lots of rows but I want the
>top ten (say).
>
>ie. select something, count(*)
> from a_table
> group by something
> order by something;
>
>I'd like the top ten something's, how do I do this?
>
>Thanks in advance. Michael.
>--
>Michael Collier (Programmer) The Computer Unit,
>Email: M.P.Collier_at_uk.ac.city The City University,
>Tel: 071 477-8000 x3769 London,
>Fax: 071 477-8565 EC1V 0HB.
>

Try this trick;

 select something, count(*)
   from a_table x
  where 10 > (select count(*)

                from a_table y
               where y.something > x.something
               group by something)

  group by something
  order by something desc
 / Received on Wed Apr 21 1993 - 04:31:24 CEST