Re: Many aggregates in one query

"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 - 18:54:56 CEST