Re: How to select first N rows?

Xavi Cheto wrote: > > Cosmo Lee <"* N O - S P A M * cosmo"@echonyc.com> escribi' en art'culo > <347780AD.D83651DE@echonyc.com>... > > Is there a way to select the first N rows from a table? > > > > i.e.: select the first 20 row from a table > > > > Try : > > SELECT field FROM table t1 > WHERE 20 > ( SELECT COUNT(*) FROM table t2 > WHERE t1.field > t2.field ) > ORDER BY field> > This selects the first 20 records of the following SELECT : > > SELECT field FROM table > ORDER BY field> > Hope that helps. YIKES! It might give'm what he wants but at the cost of scanning the whole blessed table to get the results of a query with a correlated subquery! The more essential question might be: Is there a way to tell the engine to return oly the first 20 (or N) rows of a query? Sally's 4GL solution does not quite cut it, since by the time the app has gotten 20 rows from the engine, the engine itself has internally set up for the first 87 (or some other arbitrarily large count) rows. Thus, it fails as a time saver, the primary reason for desiring such a feature. (Are you listening right now, Art? ;-) I have heard rumors that in 7.3 there is a syntax for telling the engine to stop searching after finding the first N rows for a query. If someone knows of an earlier release with this feature and/or the syntax for this, please post it here. I think it was something like: select first(20) column, column,.... from .. blah... Corrections please! -- -- Jake (Never yelled "CROWDED THEATER!" during a fire) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+