sql - 查询得到 "MAX COUNT"

Question

伙计们，我有以下问题:

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

此查询返回每个部门中的项目数。但是我不能只得到项目数量较多的部门。

我一直在尝试使用它来检索拥有最多项目的部门的名称和 ID

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

但是我一直收到错误 ORA-00936，我不知道为什么。我知道我不能使用 MAX(COUNT(*)) 但有没有办法解决这个问题？

Answer 1

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

也可以看看 howto limit the records in Oracle .