Skip to Main Content
  • Questions

  • Query to find out the owner who has maximum no of objects

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, dasarathi.

Asked: November 11, 2012 - 9:18 am UTC

Last updated: November 14, 2012 - 10:13 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,
I need to find out the owner name who have created maximum no objects.
(Including the no of objects)

I have written this query to find out the user who created maximum no of tables:

select * from
(select count(*) no,owner from all_tables group by owner)
where no in (select max(count(*))
from all_tables group by owner
)

is it perfect ?

Regards,
Das

and Tom said...

hey, it is my interview question :)

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3481455089259


question for you - what happens if you have more than one owner with the maximum number of objects??? the question is ambiguous!

do you want

a) a random row that represents one of the owners that have the most?
b) a row selected after sorting by some other criteria that represents one of the owners with the most?
c) the set of rows representing the set of owners with the most objects


here are approaches to a,b,c in order:

ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t as select owner from all_objects;
ops$tkyte%ORA11GR2> insert into t select 'SYS2' from t where owner = 'SYS';
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from (
  3  select owner, count(*) cnt
  4    from t
  5   group by owner
  6   order by cnt DESC
  7         )
  8   where rownum = 1
  9  /

OWNER                                 CNT
------------------------------ ----------
SYS2                                31468

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from (
  3  select owner, count(*) cnt
  4    from t
  5   group by owner
  6   order by cnt DESC, owner
  7         )
  8   where rownum = 1
  9  /

OWNER                                 CNT
------------------------------ ----------
SYS                                 31468

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select *
  2    from (
  3  select owner, count(*) cnt, rank() over (order by count(*) DESC) rnk
  4    from t
  5   group by owner
  6         )
  7   where rnk = 1
  8  /

OWNER                                 CNT        RNK
------------------------------ ---------- ----------
SYS2                                31468          1
SYS                                 31468          1

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sorry TOM

Dasarathiratha, November 12, 2012 - 11:11 am UTC 

Hi Tom,
sorry that i am unaware that it is ur interview question.I got it from the link that u have provided.

Ans:What ever the query i have written that can find all the users who have the maximum no of objects.

I saw ur queries,these are also good.

But i have modified the query to another format:


SQL> select count(1) no,owner  from all_tables
group by owner
having no=(select max(count(1)) from all_tables group by owner)

But sorry TOM,furhter i will review my quesiton and if it seems to be appropriate for then i will ask you

Tom Kyte
November 14, 2012 - 10:13 am UTC

don't be sorry - I found it amusing, it was a variation on a theme, a query I am very familiar with ;)

never use count(1) - that is silly, it makes you look bad.


why would you count the number of non-null number ones when you wanted to count the number of rows?

count(*) counts rows
count(1) counts the number of non-null number ones

count(1) - we had to file a bug and write an optimization that turns count(1) into count(*) so it count(1) wouldn't run for so long!

count(*) is what you mean to use!!!

Thanks Tom

dasarathi, November 14, 2012 - 2:34 pm UTC

thanks a lot TOM..

KEEP counting

Duke Ganote, November 14, 2012 - 4:05 pm UTC

select MIN(owner) KEEP (dense_rank first order by cnt desc)
AS first_owner_with_the_most
, COUNT(owner) KEEP (dense_rank first order by cnt desc)
AS how_many_owner_with_the_most
, max(cnt)
AS the_most
from (
select count(*) cnt
, owner
from dba_objects
group by owner
);

FIRST_OWNER_WITH_THE_MOST HOW_MANY_OWNER_WITH_THE_MOST THE_MOST
------------------------------ ---------------------------- ----------
SYS 1 9822

Example with duplicates:

select MIN(owner) KEEP (dense_rank first order by cnt desc)
AS first_owner_with_the_most
, COUNT(owner) KEEP (dense_rank first order by cnt desc)
AS how_many_owner_with_the_most
, max(cnt)
AS the_most
from (
select 2 as cnt, cast('Tom' as varchar2(30)) as owner from dual union all
select 2 as cnt, 'R' as owner from dual union all
select 2 as cnt, 'Kyte' as owner from dual union all
select 1 as cnt, 'me' as owner from dual
);

FIRST_OWNER_WITH_THE_MOST HOW_MANY_OWNER_WITH_THE_MOST THE_MOST
------------------------------ ---------------------------- ----------
Kyte 3 2

Interview question

Rajeshwaran, Jeyabal, November 19, 2012 - 7:30 am UTC

hey, it is my interview question :)

Exactly, I answered your question long before. (do ctrl+f 'Rajesh' )

http://tkyte.blogspot.com/2009/05/interview-question.html
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.