Re: how to make this query ?

Hello,

Could you please try the following SQL.

select a.deptno, a.deptname, count(b.name) from dept a, emp b
where b.deptno = a.deptno
group by a.deptno, a.deptname
having count(b.name) =

        (select max(empcnt)
         from (select deptno, count(name) as empcnt from emp group by deptno));

Regards
Bhooshan

pratik <pratikk_at_iprimus.com.au> wrote in message news:<Xns9207E21282649pratikkhasnabis_at_203.134.67.67>...
> I have two tables for employees and department.
> 1. EMP
> columns are NAME (varchar) and DEPTNO (number)
> 2. DEPT
> columns are DEPTNO (number) DEPTNAME (varchar)
>
> the task is to find the department with largest no of employees with a
> single query.
> i.e to show the DEPTNAME , DEPTNO and no. of employees.
>
> I am not a student , this was resulting from a discussion with a colleague.
> I have very basic knnowldege of SQL. The constraint is not to use temporary
> tables , views , PL/SQL and other oracle specific feature. ANSI SQL
> solution is most preferred.
> I have only mySQL at home to try this which doesn't support sub select.
>
> My query so far is
>
> SELECT DEPTNO , count(*) AS TOTAL FROM EMP GROUP BY DEPTNO ORDER BY TOTAL
> DESC LIMIT 1
>
> This however only gives me the DEPTNO only of the department which has
> highest employee. How do I go about to select DEPTNAME now. Also the LIMIT
> 1 construct is probably not supported by oracle.
>
> If anyone can formulate the query in oracle. I can test it at my work.
>
> pratik
Received on Thu May 09 2002 - 06:48:09 CEST