Re: Many aggregates in one query

If you're using mySQL you can use the LIMIT clause which is specific to this DBMS and do the following :
SELECT wsid, count(wsid) AS rowcnt
FROM test
GROUP BY wsid
ORDER BY rowcnt ASC
LIMIT 1; You just get the first result.

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:37:16 CEST