Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Top 10 solution in Oracle 8.0
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
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 )
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