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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Top N query

RE: Top N query

From: Muthu Avudaiyappan <MAvudaiyappan_at_Vertek.com>
Date: Wed, 10 Jan 2001 15:59:31 -0500
Message-Id: <10737.126336@fatcity.com> 





Hi Lisa,



Here is the Code  for  8i ( The script will NOT  run in an earlier
  version as it relies on several different Oracle8i specific features )



Muthu - New Jersey




  This script demonstrates how to fetch the top n occurences of column 2 for
each


  distinct occurence of column 1, for example say the 2 highest employee
numbers 


  for each department in the emp table.



  This SQL*Plus script should be run as user SCOTT (or as a user who has
access to


  the EMP table) in an Oracle8i database. 



  REM



  REM  This script demonstrates how to fetch the top n occurences of column
2


  REM  for each distinct occurence of column 1, for example say the 2
  REM  highest employee numbers for each department in the emp table.
  REM



  REM  In this example we will initially use simple numeric data to
demonstrate 


  REM  the functionality.


  REM 



  REM  The result is achieved with the use of a nested table type - the data



  REM  from which we wish to take the top n records (eg empnos in the above 
  REM  example) is cast to a nested table type then unnested again using the
  REM  new Oracle8i collection unnesting feature.  
  REM



  REM



  REM  Create the table and data.


  REM



  drop table top


  /


  create table top (c1 number, c2 number)
  /


  insert into top values (1,1)


  /


  insert into top values (1,2)


  /


  insert into top values (1,3)


  /


  insert into top values (1,4)


  /


  insert into top values (1,5)


  /


  insert into top values (2,1)


  /


  insert into top values (2,2)


  /


  insert into top values (2,3)


  /


  insert into top values (2,4)


  /


  insert into top values (2,5)


  /


  insert into top values (3,1)


  /


  insert into top values (3,2)


  /


  insert into top values (3,3)


  /


  insert into top values (3,4)


  /


  insert into top values (3,5)


  /


  commit


  /


  REM



  REM  Fetch the top 3 column c2 values for each distinct column c1 value.
  REM



  REM  Looking at the following query from the inside out:
  REM



  REM  a)  order all the rows in the table, TOP, with c1 ascending and c2

  REM      descending via an in-line view.  Note the use of order bys in 
  REM      in-line views is an Oracle8i feature.
  REM  b)  from this returned data take the top 3 values of c2, via rownum
  REM      (since the data is already ordered), for the current value of c1.


  REM  c)  cast the 3 returned values of c2 to a nested table of numbers.
The 

  REM      reason a nested table is used is because if we were to simply use
  REM      another in-line view for the select where rownum then we would
  REM      not be able to tie this to the current row in the select from TOP
  REM      itself.  Thus we would only be able to return the top 3 rows in
for
  REM      the first value of c1.  By casting the returned data to a nested 
  REM      table and then using the TABLE function to unnest it again we can

  REM      perform a correlated subquery and so achieve what we require.


  REM  d)  use the Oracle8i TABLE function to treat the nested table as
though

  REM      it is a real table along side TOP.  So TOP gives us the values of
  REM      c1 and the TABLE function returns the top 3 values of c2 that 
  REM      correspond to that value of c1.


  REM  e)  the distinct and order by on the outer query then return the data
in 


  REM      the correct order and prevent data being duplicated for every row
in


  REM      TOP.  



  REM



  REM  First create the nested table type we are going to use.
  REM



  create or replace type num_tab is table of number;
  /


  REM



  REM  Now run the query.


  REM



  select distinct c1, column_value c2


  from top a, 

       table ( cast ( multiset ( select c2 
                                 from ( select c1, c2 from top 
                                        order by c1, c2 desc ) t
                                 where t.c1 = a.c1 and rownum <= 3 ) 
                      as num_tab ) )




  order by c1, c2 desc


  /


  REM



  REM  Gives:


  REM



  REM          C1         C2



  REM  ---------- ----------


  REM           1          5
  REM           1          4
  REM           1          3
  REM           2          5
  REM           2          4
  REM           2          3
  REM           3          5




  REM



  REM



  REM  Because the table TOP could potentially be very large the inner order
  REM  by could take a long time.  Hence performance could be improved by
using 


  REM  an index in place of the inner order by.  The outer order by only
needs 


  REM  to order the returned data and so should not be a major overhead.
  REM



  REM



  REM  Create the index - ascending on column c1, descending on c2.
  REM



  create index i_top on top (c1 asc, c2 desc);
  REM



  REM  If you now re-run the query, as is, then in 8.1.5 you will get
ORA-3113 



  REM  with ORA-7445 in the corresponding trace file.  This is bug 869177,
fixed 


  REM  in 8.1.6, which is a problem with an order by descending in an
in-line 


  REM  view when you have a descending index.
  REM



  REM  With the above index, if you remove the order by and do not add an
index 


  REM  hint to the inner most query then the in-line view seems to get
merged 


  REM  with the outer one at execution time so the result is no rows
returned.  


  REM  The same happens when the above index is used along with the rule
based 


  REM  optimiser.  Using the cost based optimiser with an index hint does
return 


  REM  the required data.


  REM



  select distinct c1, column_value c2


  from top a,

       table ( cast ( multiset ( select c2
                                 from ( select /*+ INDEX(top,itop) */
                                        c1, c2 from top ) t
                                 where t.c1 = a.c1 and rownum <= 3 )
                      as num_tab ) )




  order by c1, c2 desc


  /


  REM



  REM  Gives output as before.


  REM



  REM



  REM  To now restrict the values of c1 we wish to look at we simply need to
  REM  add a where clause to the inner most query.
  REM



  select distinct c1, column_value c2


  from top a,

       table ( cast ( multiset ( select c2
                                 from ( select /*+ INDEX(top,itop) */
                                        c1, c2 from top
                                        where c1 in (1,3) ) t
                                    where t.c1 = a.c1 and rownum <= 3 )
                      as num_tab ) )




  order by c1, c2 desc


  /


  REM



  REM  Gives:


  REM



  REM          C1         C2



  REM  ---------- ----------


  REM           1          5
  REM           1          4
  REM           1          3
  REM           3          5
  REM           3          4
  REM           3          3




  REM



  REM 



  REM  Lastly to demonstrate the example first mentioned - the two highest 
  REM  employee numbers for each department (with no index on emp).
  REM



  select distinct deptno, column_value empno
  from emp a,

       table ( cast ( multiset ( select empno 
                                 from ( select deptno, empno from emp
                                        order by deptno, empno desc ) t
                                 where t.deptno = a.deptno 
                                 and rownum <= 2 )
                      as num_tab ) )




  order by deptno, empno desc


  /


  REM



  REM  Gives:


  REM



  REM      DEPTNO      EMPNO



  REM  ---------- ----------


  REM        10       7934
  REM        10       7839
  REM        20       7902
  REM        20       7876
  REM        30       7900
  REM        30       7844




  REM









> -----Original Message-----

> From:	Koivu, Lisa 

> Sent:	Wednesday, January 10, 2001 3:01 PM

> To:	Multiple recipients of list ORACLE-L

> Subject:	Top N query

> 

> OK, I apologize in advance, I know this has been discussed many times. 

> 

> I need to generate the top N, in this case 3, values for a query, like the

> example shown below.  It has to be done in SQL, because I need to return

> this as a refcursor.  (OPEN refcurcor FOR...) 

> 

> category1     10 

> category1     14 

> category1     2 

> category1     25 

> category2     14 

> category2    13 

> category2     55 

> category2    5 

> category3    14 

> category3    1 

> category4    5 

> 

> I need to display this: 

> 

> category1  25 

> category1  14 

> category1  10 

> category2  55 

> category2  14 

> category2  13 

> category3  14 

> category3  1 

> category4  5 

> 

> Can you hear me cursing.... 

> 

> Thanks in advance for any suggestions.  

> 

> Lisa Rutland Koivu 

> Oracle Database Administrator 

> Qode.com 

> 4850 North State Road 7 

> Suite G104 

> Fort Lauderdale, FL  33319 

Received on Wed Jan 10 2001 - 14:59:31 CST












Original text of this message













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