lte__ lte__ - 18 days ago 10
SQL Question

Oracle SQL - Group by with ROWNUM in Having clause?

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

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;