Re: Use ROWNUM with GROUP BY

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 20 Nov 1999 16:36:47 -0500
Message-ID: <j15e3sce2quv1uka8sc8p6jge5qh37o880@4ax.com>

A copy of this was sent to Greg Stark <greg-spare-1_at_mit.edu> (if that email address didn't require changing) On Sat, 20 Nov 1999 19:00:13 GMT, you wrote:

>
>Ordered subqueries are a feature of 8i
>
>Do do this in 8.0.5 the only way i see is to create a temporary table, do the
>select into the table then select from the table with an order by.
>

Another way in 8.0 and up would be to select * from PLSQL function. for example:

tkyte_at_8.0> create or replace type myScalarType as object ( cnt int, table_name varchar2(30) )
2 /

Type created.

tkyte_at_8.0> create or replace type myTableType as table of myScalarType; 2 /

Type created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace function getMyTableType( p_rows_to_return in number

) return myTableType
2 as
3 l_x myTableType := myTableType(); 4 begin

  5          for x in ( select count(*) cnt, table_name
  6                                   from user_tab_columns
  7                                  group by table_name
  8                                  order by 1 desc )
  9          loop
 10                  exit when l_x.count = p_rows_to_return;
 11                  l_x.extend;
 12                  l_x(l_x.count) := myScalarType( x.cnt, x.table_name );
 13          end loop;
 14          return l_x;

15 end;
16 /

Function created.

tkyte_at_8.0>
tkyte_at_8.0> select *
2 from THE ( select cast( getMyTableType(3) as mytableType ) from dual ) a 3 /

CNT TABLE_NAME

---------- ------------------------------
         8 EMP
         2 SHOW
         2 T1

>I suppose you could force it to be ordered by joining it against an indexed
>table that had all the integers and forcing an descending index scan and a
>merge join. Probably easier to just use a temporary table.
>
>"Tom Williamson" <tom.williamson_at_home.com> writes:
>
>> When I execute your query I get :
>>
>> SQLWKS> SELECT * FROM (
>> 2> SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY
>> table_name order
>> 3> by count(*) desc
>> 4> )
>> 5> WHERE ROWNUM < 5;
>> SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY table_name order
>> *
>> ORA-00907: missing right parenthesis
>>
>>
>> The "missing right parenthesis" occurs at the ORDER clause of the subquery.
>> (Note: I also tried the same code as a view.) Does this really work on
>> your system? What version of Oracle are you running? I'm running 8.0.6.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Nov 20 1999 - 15:36:47 CST

Re: Use ROWNUM with GROUP BY - ARRGH!