Re: sql : the 2 best, how ???

Luc Hendrickx <paratel_at_mail.interpac.be> wrote:
: 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)

-- 

Hi,

I think this statement should work:

select emp_c.deptno,emp_c.emp_count
from   (select deptno,count(*) emp_count
        from   emp
        group by deptno) emp_c
where   2 > (select count(*)
             from   (select count(*) emp_count
                     from   emp
                     group by deptno)  emp_c2
             where   emp_c2.emp_count > emp_c.emp_count)
order by emp_c.emp_count desc
/

By changing the number that are checked against the subquery you could adjust
the number of rows returnied. If you want to return the 1000 departments with
the highest empoyee count, just change the number from 2 to 1000.

Rgds
Steinar Heggelund


----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.

My postings represent my opinions and they may be wrong.