Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> top N or rewriting MAX
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.caReceived on Mon Jan 06 1997 - 00:00:00 CST