Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using a MIN with a COUNT
martinl <laflamme_at_iname.com>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦
news:C_x_3.1088$ym.59835_at_wagner.videotron.net¿¡ °Ô½ÃÇÏ¿´½À´Ï´Ù.
> 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.
>
> Thanks a lot,
> Martin
>
>
How about this statement?
SELECT MIN(CNT), nbDesks
FROM
(SELECT COUNT(nbDesks) CNT, nbDesks FROM rooms GROUP BY (nbDesks));Received on Tue Nov 23 1999 - 11:55:17 CST