Re: Top n in sql

Örjan Lundberg wrote:
>
> select userid, count FROM (
> select userid, count(*) as count from A
> group by userid
> order by 2 desc)
> where ROWNUM <6;
>
> should do it.
> <genebarkin_at_my-deja.com> wrote in message
> news:7j11rv$95d$1_at_nnrp1.deja.com...
> > add in a having clause after the group by:
> > HAVING count(*) >= 5
> >
> >
> > In article <7ic8td$ao3_at_news.abbott.com>,
> > "Doug Brown" <Doug.Brown_at_abbott.com> wrote:
> > > I want to return the top 5 users of yesterdays
> > internet. I have a log table
> > > that has userid and date visited.
> > >
> > > select count(*)
> > > from logtable
> > > group by userid
> > > order by count(*) desc
> > >
> > > but I want to stop after the top five are
> > returned.
> > >
> > > Anyone know this one?
> > >
> > > Thanks
> > > Doug Brown

The above solution does not appear to work in Oracle 7.3.3, apparently because of the ORDER BY in the sub-select. Is this restricted to 8.x?

Thanks,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Sun Jun 06 1999 - 17:00:50 CDT