Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using a MIN with a COUNT
SELECT COUNT(nbDesks), nbDesks
FROM rooms
GROUP BY nbDesks
HAVING COUNT(nbDesks) = (SELECT MIN(COUNT(nbDesks))
FROM rooms GROUP BY nbDesks)
In article <81epj7$btf$1_at_nnrp1.deja.com>,
lily99_at_my-deja.com wrote:
> I think you can try this way:
>
> SELECT COUNT(nbDesks), nbDesks
> FROM rooms
> GROUP BY (nbDesks)
> HAVING COUNT(nbDesks) = MIN(COUNT(nbDesks))
> ORDER BY 1;
>
> Hope this will help.
>
> In article <C_x_3.1088$ym.59835_at_wagner.videotron.net>,
> "martinl" <laflamme_at_iname.com> 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.
> >
> > Thanks a lot,
> > Martin
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 23 1999 - 14:15:14 CST