Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Top 10 solution in Oracle 8.0

Top 10 solution in Oracle 8.0

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Jun 1999 11:14:36 +0100
Message-ID: <930738021.16389.0.nnrp-13.9e984b29@news.demon.co.uk>

A method for getting the Top N items

Cast the ordered query into an object array. Convert the (ordered) array into a cursor Apply ROWNUM to the cursor.

For example:

rem A type to represent the row
create or replace type junk as object (id number , name varchar2(30));
/

rem A type for the array of rows
create or replace type junk_array as table of junk;
/

rem Create and populate a demo table create table jpl_demo (

     id,
     name

)
as
select rownum, table_name
from all_tables
where rownum <= 10
;

rem Check the contents of the demo
select * from jpl_demo;

select *
from

     the

          select
               cast(
                    multiset(
                         select * from jpl_demo order by name
                    ) as junk_array
               )
          from dual
     )

where rownum <= 3
;

drop type junk_array;
drop type junk;
drop table jpl_demo;

cast(multiset()) turns the query into an array

    but the query introduces the order you want the(select()) turns the array back into a cursor set

    but this is now ordered, and rownum has not yet been applied

WARNING:
    If you try this on Oracle 8.1.5, there is a bug-     the only column in the select that comes out correctly is the     column you have ordered by. All other columns seems to     product the value of the last row in the original select.     NB You don't need to do it this way on 8.1.5 anyway

WARNING 2
    You need to licence the object option in 8.0 to do this

WARNING 3
    The trick still has to select and sort all the data, then cast     the entire set into an array, do not expect brilliant performance     for large data sets; do expect the process to consume a large     amount of memory, and do expect the process to crash for     VERY large datasets

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Wed Jun 30 1999 - 05:14:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US