Re: how to make this query ?

a)
select deptno
 from (select deptno, count(*)

         from emp
         group by deptno
         order by 2 desc)

this is oracle specific, but if ansi has equiv to rownum, should work with ansi too.

b)

   select deptno

         from emp
         group by deptno
         having count(*) >=all (select count(*)
                                from emp
                                group by deptno)

-MS

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 Tue May 07 2002 - 20:38:50 CEST