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).
You might try something like this:

```
SELECT dept, num_items FROM (
    SELECT dept, COUNT(*) AS num_items
         , ROW_NUMBER() OVER ( ORDER BY COUNT(*) DESC ) AS rn
      FROM desc_look
     GROUP BY dept
) WHERE rn = 1;

```