Re: Simple Query [Q]

From: The Wils <awislon_at_rci.rogers.com>
Date: 1997/05/14
Message-ID: <3379DE2C.7832@rci.rogers.com>#1/1

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 ?
>

offhand, it would go something like this...

  1 select deptno, count(staff) "no of staff"   2 from dept
  3 having count(staff) = (select max(count(staff))

  4                          from dept
  5                          group by deptno)

  6* group by deptno

The key part is the 'having' clause
Have fun....

Alan. Received on Wed May 14 1997 - 00:00:00 CDT