Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a854bb0vv0_at_drn.newsguy.com>...
> In article <be3be537.0203301104.283dcd29_at_posting.google.com>,
> dkniveton71_at_msn.com says...
> >
> >I have two questions on this topic.
> >First if I query my user_tab_columns I have about 1062 column names.
> >How do I query to figure out which one is used the most?
> >
>
> you can use:
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select *
> 2 from ( select column_name, count(*)
> 3 from user_tab_columns
> 4 group by column_name
> 5 order by 2 DESC )
> 6 where rownum = 1
> 7 /
>
> COLUMN_NAME COUNT(*)
> ------------------------------ ----------
> DEPTNO 4
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from
> 2 (
> 3 select column_name, dense_rank() over ( order by cnt DESC ) dr
> 4 from(select column_name, count(*) cnt
> 5 from user_tab_columns
> 6 group by column_name )
> 7 )
> 8 where dr = 1
> 9 /
>
> COLUMN_NAME DR
> ------------------------------ ----------
> DEPTNO 1
> X 1
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select column_name, count(*)
> 2 from user_tab_columns
> 3 group by column_name
> 4 having count(*) =
> 5 ( select max(count(*)) from user_tab_columns group by column_name);
>
> COLUMN_NAME COUNT(*)
> ------------------------------ ----------
> DEPTNO 4
> X 4
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
>
> but as you see -- you might get different answers using different techniques.
> In this schema - there were 2 columns that occured "most frequently"
>
>
> >Second, if I query my all_users table I get a list of about 200 users
> >created on different dates. How do I find out which one was created
> >most recent (by date only)? I know I can do an order by created but I
> >lose the the top of the list because it is too long to keep in the
> >buffer. Isn't there an easier way?
> >
>
> select * from all_users
> where trunc(created) = ( select max(trunc(created)) from all_users );
>
>
> >Thanks for any help on this.
Thank you very much for your help Thomas. I like the first select the
best for the first question but will play around with all three.
Thanks again.
Received on Sun Mar 31 2002 - 10:26:37 CST
