Tuesday, August 28, 2012


This was tested on Oracle 11.2. You might think that, if you wanted to see details from a row containing some maximum value, you could do it as follows:
SQL> select owner, segment_name, max(bytes)
  2  from dba_segments
  3  /
select owner, segment_name, max(bytes)
ERROR at line 1:
ORA-00937: not a single-group group function
... but you get an ORA-00937. You have to do it like this:
SQL> l
  1  select owner, segment_name, bytes
  2  from dba_segments
  3  where bytes =
  4* (select max(bytes) from dba_segments)
SQL> /
OWNER           SEGMENT_NAME         BYTES
--------------- --------------- ----------
SYS             IDL_UB1$         251658240

No comments: