Oracle SQL - Group by with ROWNUM in Having clause?

Question

I'm trying to get the user in the database who has the ownership over the biggest segment in the database. For this I'm trying:

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

This, however, returns "not a GROUP BY expression". Why can't I select the first row only? How can I write this query? Thank you!

Answer 1

You can. In Oracle 12c+, you can do:

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

Note the ORDER BY.

In earlier versions you need a subquery:

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

In earlier versions, you can also use (just one pass over the data):

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