how to make this query ?

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 - 14:47:14 CEST