Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple Query [Q]
Something like:
1 select deptno, count(*)
2 from emp
3 group by deptno
4 having count(*) = ( select max( count(*) )
5 from emp 6* group by deptno )SQL> / DEPTNO COUNT(*)
30 6
OR
1 select deptno, count(*)
2 from emp
3 group by deptno
4 having count(*) >= ALL ( select count(*)
5 from emp 6* group by deptno )SQL> / DEPTNO COUNT(*)
30 6
will do it.
On 13 May 1997 13:00:17 GMT, 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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities