Question: Guys I have the following query: 

```
select count(*) AS num_items
       from desc_look
       group by dept

```

This query returns the number of items in each department. However I can't get ONLY the department with the higher number of items.

I've been trying to use this to retrieve the name and id of the department with most items

```
select dept, desc_up
from desc_look
where (select count(*) AS num_items
       from desc_look
       group by dept)

```

However I keep getting an error ORA-00936 and I don't know why.
I know I can't user MAX(COUNT(\*)) but is there a way to workaround this?

Answer: 
```
select * from
(
    select count(*) AS num_items
    from desc_look
    group by dept
    order by count(*) desc
) tmp
WHERE ROWNUM = 1;

```

Also have a look on [howto limit the records in Oracle](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering).
This version is basically the same as juergen's, but using an analytic function instead of an aggregate (`GROUP BY`) for counting:

```
SELECT t.dept, t.desc_up FROM
  (SELECT dept, desc_up,
   COUNT(*) over (partition BY dept) dept_count
   FROM desc_look
   ORDER BY dept_count DESC
  ) t
WHERE rownum = 1

```

If you're on Oracle 12, the inline view is not needed because you can use the row-limiting clause (`FETCH FIRST ...`):

```
SELECT dept, desc_up,
  COUNT(*) over (partition BY dept) dept_count
FROM desc_look
ORDER BY dept_count DESC
FETCH FIRST 1 ROW ONLY

```