Most

Find the value that occurs most often in a column. For example, @most object_type all_objects reports "VIEW" and the number of occurrences; or @most object_type all_objects 5 to see the top 5 object types.

-- Find value with the [n] most occurrences, e.g:
--
-- SQL> @most object_type dba_objects
--
-- OBJECT_TYPE             TOTAL
-- ------------------ ----------
-- INDEX                    7225
--
-- SQL> @most object_type dba_objects 3
--
-- OBJECT_TYPE             TOTAL
-- ------------------ ----------
-- INDEX                    7225
-- TABLE                    4522
-- VIEW                     2724
--
-- Parameter &3 is redefined as bind variable :limit which defaults to 1.
-- Note that "&1" can be any valid column list, most easily specified by omitting
-- spaces (though double-quotes would also work)
--
-- SQL> @most owner,object_type dba_objects
--
-- OWNER                          OBJECT_TYPE             TOTAL
-- ------------------------------ ------------------ ----------
-- SYS                            VIEW                     1910
--
-- Likewise "&2" can be any valid expression equivalent to a table etc etc
--
-- load_params.sql initialises default parameters in SQL*Plus - see
-- http://www.williamrobertson.net/code/load_params.sql.txt

-- William Robertson, www.williamrobertson.net


set term off autoprint off serveroutput on
@load_params

def column = &1
def table = &2
def limit = &3
var limit number
var plural varchar2(1)

store set sqlplus_settings.sql replace
set term on feed off

begin
   :limit := nvl('&3',1);
   if :limit > 1 then
      :plural := 's';
   end if;

   dbms_output.put_line('Results limited to ' || :limit || ' row' || :plural);
end;
/

col value hea dummy
col value clear

select value
     , total
from   ( select &1 as value
              , count(*) as total
         from   &2
         group  by &1
         order by total desc )
where  rownum <= :limit
/

undef 2
undef 3
undef 4

@sqlplus_settings.sql
set term on
prompt