Re: Simple Query [Q]

hello,
  The basics of the query is:

select

   deptno,
    count(staff) "No of Staff"
from

    <staff table>
group by

   deptno
order by

   "No of Staff" desc;

If you are doing this in PL/SQL and only want the maximun then then make it into a cursor and fetch the first row.

You cannot use it as a subquery because of the 'order by' clause.

graham   

ckkan_at_cs.hku.hk (Kan Chun Kin (CE)) wrote...

| hi,
|
| how can i find the deptno that contains most staff and the number
| of staff in that dept from the following relation by a single query
| in sqlplus ?
|
| staff deptno
| ------ --------
| staff1 d1
| staff2 d1
| staff3 d2
| staff4 d2
| staff5 d2
| staff6 d3
| staff7 d3
| staff8 d3
| staff9 d4
|
| so sqlplus would return
|
| deptno no of staff
| ------- ------------
| d2 3
| d3 3
|
|
| thx for help :>
|
| -- ckkan

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Thu May 15 1997 - 00:00:00 CDT