Re: Many aggregates in one query

How about
SELECT col1,COUNT(*) FROM IN1 group by col1 order by count(*),col1 fetch first 1 rows only
Thanks
Aakash
"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0108170854.1c237116_at_posting.google.com...
> "Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message
 news:<9lh091$pfg$1_at_stlnews.stl.ibm.com>...
> > There may be a 1000 ways to optimize this :)
> >
> > select col1,count(*) from in1,
> > (select min(col1) as m from (select col1,count(*) from in1 group by col1
> > having count(*)=(select min(cnt) as cnt from (select count(*) as cnt
 from
> > in1 group by col1) as a) ) as c) as d where col1=m group by col1
> >
>
> I doubt you can optimise it significantly. Here is a version without
> "having":
>
> select min(id), rowcnt from (select id, count(1) rowcnt from test
> group by id)
> where rowcnt in (
> select min(rowcnt) from (
> select count(1) rowcnt from test group by id
> )
> )
> group by rowcnt
>
>
> Straightforward "aggregate by count, then by id"
>
> select min(id) minnum, rowcnt from (
> select id, count(1) rowcnt from test group by id
> ) group by rowcnt
>
> gives a head start:
>
> MINNUM ROWCNT
> ---------- ----------
> 1001 2
> 1003 3
> 1000 4
>
> so that the problem is reduced to selecting the first row only with
> correlated subquery tecnique. Unfortunately, on this path we have to
> use
>
> select id, count(1) rowcnt from test group by id
>
> in 2 places, which would make the query eventually as complex as the
> versions above. In short, since your original table doesn't contain a
> counter, we have to create one in an [inner] view, and then use it in
> more than one place.
Received on Fri Aug 17 2001 - 23:14:10 CEST