Re: SQL: How to select first N rows?

In article <347780AD.D83651DE@echonyc.com>, Cosmo Lee <"* N O - S P A M * cosmo"@echonyc.com> writes >Is there a way to select the first N rows from a table? > >i.e.: select the first 20 row from a table Not last time I looked. Unless you are running a query which can be satisfied directly from an index (e.g. single table & 'order by' matches columns in an index) the query has to be run to completion so that the first N rows can be defined. If you are using 4GL and the query matches the above condition (use 'SET EXPLAIN' to check) the following should do the trick: LET n = 20 # Set value for N DECLARE get_rows CURSOR FOR my-query OPEN get_rows LET l_count = 0 WHILE l_count < n AND STATUS = 0 FETCH get_rows INTO my-variables IF STATUS != 0 THEN EXIT WHILE END IF LET l_count = l_count + 1 process-my-variable # code to do what you # will with the row END WHILE Please note I havn't tested this code and maybe a widget has arrived in version 7 that I don't know about. Maybe you have an Ingres backgound as Ingres V5 did have a widget, though it also would often have to run the query to completion to determine *what* the first n rows were. Presumably the widget remained in later versions of Ingres. My favourite Ingres 5 widget not in Informix is: CREATE TABLE mytable1 LIKE mytable (or may 'AS mytable) (maybe 'mytable.*) Very useful. Saved a lot of typing & round-the-houses method. Note that the memory has faded to some extent.... :) -- Sally Woolrich My Email address has been altered to limit junk mail. Please remove the second 'x' in the company name to Email me.