Re: Using a MIN with a COUNT

A copy of this was sent to "martinl" <laflamme_at_iname.com> (if that email address didn't require changing) On Tue, 23 Nov 1999 10:04:37 -0500, you wrote:

>Greetings to all,
>
> I have a small question regarding the combined use of the MIN and COUNT
>set functions. I have a querry which looks like this:
>
> SELECT COUNT(nbDesks), nbDesks
> FROM rooms
> GROUP BY (nbDesks)
> ORDER BY 1;
>
> This will return the following results:
>
>COUNT(NBDESKS) NBDESKS
>-------------- --------------------
> 2 d2
> 3 d1
> 3 d4
> 4 d3
>
> Now, using the COUNT(nbDesks) column as a referrence, I would like to
>retrieve only the min value of this table, i.e the first line. My
>question is: is there any way to use the MIN function with the COUNT
>function to achieve this? As a first try, I attempted to combine them in
>the following
>way:
>
> SELECT MIN(COUNT(nbDesks)), nbDesks
> FROM rooms
> GROUP BY (nbDesks)
> ORDER BY 1;
>
> But that didn't work. Anybody has an idea? I know I could use a cursor
>to retrieve the first row but I was wondering if there was a more direct way
>to do this.
>

scott_at_8.0> select count(*), deptno
  2 from emp
  3 group by deptno
  4 /

  COUNT(*) DEPTNO
---------- ----------

         3         10
         5         20
         6         30

scott_at_8.0>
scott_at_8.0> select count(*), deptno
  2 from emp
  3 group by deptno
  4 having count(*) = ( select min(count(*)) from emp group by deptno )   5 /
ou
  COUNT(*) DEPTNO
---------- ----------

         3 10

but beware -- if there exists >1 row with min(count(*)), you'll get them as well (this does not return exactly 1 record, could return 0, 1 or more)

> Thanks a lot,
> Martin
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 23 1999 - 09:34:50 CST