sql : the 2 best, how ???

From: Luc Hendrickx <paratel_at_mail.interpac.be>
Date: 1997/05/30
Message-ID: <338F084D.C95D873F@mail.interpac.be>#1/1

--------------BF1398E906C2871DADD12B2C
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi,

I have a serious problem and following will illustrate that by the emp - table.
I would like to get 2 department with hight personel count (ordered by count).

select deptno, count(*)
from emp
group by deptno;

this gives me :

> DEPTNO     COUNT(*)
> ---------- ----------
>         10          4
>         20          8
>         30          7
>
and I would like to have this :
          20                    8
          30                    7

I can't use rownum because it takes the 2 first rows and works on from there. I worked this on out, but suppose if you would try to do the same thing with the 300 best.

> create table deptnum as
> select deptno, count(*) numb
> from emp
> group by deptno;
>
> select x.deptno, x.numb from deptnum x
> where 0 = (select count(*) from deptnum y
>                 where y.numb > x.numb)
> or 1 = (select count(*) from deptnum y
>                 where y.numb > x.numb)
> order by x.numb desc;
>

I hope somebody could help me.

Luc Hendrickx
Paratel Interactive
Vilvoorde (Belgium)

--------------BF1398E906C2871DADD12B2C
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>

Hi,

I have a serious problem and following will illustrate that by the emp
- table.
 I would like to get 2 department with hight personel count (ordered
by count).

select deptno, count(*)
 from emp
 group by deptno;

this gives me :
<BLOCKQUOTE TYPE=CITE>
<PRE>DEPTNO     COUNT(*)  
---------- ----------

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7</PRE>

</BLOCKQUOTE>

and I would like to have this :
           20                   
8
           30                   
7

I can't use rownum because it takes the 2 first rows and works on from
there.  I worked this on out, but suppose if you would try to do the same thing with the 300 best.
<BLOCKQUOTE TYPE=CITE>
<PRE>create table deptnum as

select deptno, count(*) numb
from emp 
group by deptno;

select x.deptno, x.numb from deptnum x
where 0 = (select count(*) from deptnum y                  where y.numb > x.numb) or 1 = (select count(*) from deptnum y                  where y.numb > x.numb) order by x.numb desc;</PRE>
</BLOCKQUOTE>

I hope somebody could help me.

Luc Hendrickx
 Paratel Interactive
 Vilvoorde (Belgium)
 
 </HTML>

--------------BF1398E906C2871DADD12B2C-- Received on Fri May 30 1997 - 00:00:00 CDT