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 N or rewriting MAX

top N or rewriting MAX

From: <otierney_at_freenet.edmonton.ab.ca>
Date: 1997/01/06
Message-ID: <5aqqq8$3hu$1@news.sas.ab.ca>#1/1

Does anyone know of good (ie FAST) ways of getting the top N rows in a table?

example of ,not very efficient (?), ways: create a cursor with order by,
fetch first N rows returned.
[not good since the order by means the whole list has to be searched
[or the colum needs an index]]

select * from mytable a where a.columnname=( select MAX(b.column name) from myttable b) OR a.columnname=(
select MAX(c.columnname) from mytable c where c.columnname>b.columnname)
[gets top 2] this is bad because it requires subqueries, [and
requires an extra subquery for N=3.
<insert further ways here..>

One bizare suggestion (which I have no idea how to implement) is to write a MAX2 plsql function, which can be used in place of MAX, is this possible so I can do something like: select * from mytable a where a.columname=(select MAX2(b.columnname) from mytable b)
??

any thoughts?

Turloch

--
Turloch O'Tierney
otierney_at_freenet.edmonton.ab.ca
Received on Mon Jan 06 1997 - 00:00:00 CST

Original text of this message

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