Re: top N or rewriting MAX

otierney_at_freenet.edmonton.ab.ca () wrote:

>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]]

This is probably the best way you're going to come up with. The entire list is *always* going to need to be searched to find the top N entries regardless of how you limit the results. Only an index can prevent this.

If it's a table that frequently needs the top N extracted, perhaps you could create a trigger that maintains a 2nd much smaller table containing only the top N entries (or just their rowids). But then you lose performance every time the table is updated.

--
Chuck Hamilton
chuckh_at_dvol.com

This message delivered by electronic sled dogs. WOOF!