Re: Many aggregates in one query

Mikito Harakiri <nospam_at_newsranger.com> wrote in message news:<Tcif7.5590$2u.54105_at_www.newsranger.com>...
> In article <9lk0u0$g70$1_at_stlnews.stl.ibm.com>, Aakash Bordia says...
> >
> >How about
> >SELECT col1,COUNT(*) FROM IN1 group by col1 order by count(*),col1 fetch
> >first 1 rows only
>
> Right. Except that "fetch first 1 rows only" can be transformed into something
> more legitimate like
>
> select * from( select *, rownum rn from inner_query) where rn < 2
>
> The query becomes:
>
> select minnum, rowcnt from (
> select minnum, rowcnt, rownum rn from (
> select id, count(1) rowcnt from test group by id order by rowcnt, id
> )
> ) where rn < 2

This is indeed a good query. But this is not a general solution. For that to be the case, it has to work for the MINIMUM, not just 2 or 1, but whatever it might be (presumably unknown at the beginning). Sorry, if I offended anyone.
- Saurabh. Received on Wed Aug 22 2001 - 03:15:22 CEST