Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple Query [Q]

Re: Simple Query [Q]

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/17
Message-ID: <337daac6.814571@newshost>#1/1

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



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 17 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US