Re: Many aggregates in one query

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

Thanks
Aakash

"Matti Teppo" <matti.teppo_at_nospam.deio.net> wrote in message news:3B7BCA1D.B40FBD52_at_nospam.deio.net...
> I have a problem, for which I'm sure there is a simple answer but I
> can't see it.
>
> I've a table called "test" with following rows:
> id
> -----------
> 1000
> 1000
> 1000
> 1000
> 1001
> 1001
> 1002
> 1002
> 1003
> 1003
> 1003
>
> I want to get the smallest wsid having the smallest amount of rows.
> Result set I want is:
> wsid rowcnt
> ----------- -----------
> 1001 2
>
> The following returns all the rows that have the smallest amount of
> rows, but how to get only the smallest wsid?
> select wsid, count(wsid) as rowcnt
> from test
> group by wsid
> having count(wsid) = min(count(wsid))
>
> wsid rowcnt
> ----------- -----------
> 1001 2
> 1002 2
>
> Adding wsid = min(wsid) to having clause does not help.
>
> Thank you in advance
>
Received on Thu Aug 16 2001 - 19:44:37 CEST