Oracle SQL - Group by with ROWNUM in Having clause?

Question

我想让数据库中的用户拥有数据库中最大段的所有权。对于这个，我正在尝试:

SELECT owner, MAX(bytes) 
FROM SYS.DBA_SEGMENTS
GROUP BY owner
HAVING ROWNUM <= 1;

但是，

返回"not a GROUP BY expression"。为什么不能只选择第一行呢?我怎么写这个查询呢?谢谢你！

Answer 1

可以。在Oracle 12c+中，您可以这样做:

SELECT owner, MAX(bytes) 
FROM SYS.DBA_SEGMENTS
GROUP BY owner
ORDER BY MAX(bytes) DESC
FETCH FIRST ROW ONLY;

注意ORDER BY

在早期版本中，您需要一个子查询:

SELECT o.*
FROM (SELECT owner, MAX(bytes) 
      FROM SYS.DBA_SEGMENTS
      GROUP BY owner
      ORDER BY MAX(bytes) DESC
     ) o
WHERE rownum = 1;

Answer 2

在较早的版本中，您还可以使用(只需一次传递数据):

select max(owner) keep (dense_rank last order by bytes nulls first) as owner,
       max(bytes) as bytes
from   sys.dba_segments;