Re: SELECTing first x results (a FAQ?)

On Mon, 14 Sep 1998, SUNIL S. SINGH wrote: > you can use following SQL script... > > CREATE TABLE TEMP_T <same as table form which u want to select rows>;> > INSERT INTO TEMP_T AS > (SELECT * FROM main_table);> > SELECT * FROM TEMP_T > WHERE rowid < (SELECT MIN(rowid) + <no of rows to be selected say 50>);> > if ur main_table's rowid is serial than u can directly use above query > without creating temp table. This will only work on SE, where the ROIWD is effectively a row number. With OnLine, the ROWID in a non-fragmented table is a combination of page number and slot number, and the lowest page number is 1, so the lowest ROWID is 0x101 (257 decimal) and the numbers are not (in general) contiguous. If only 10 rows fit on a page, then the first ten ROWIDs will be 0x101..0x10A, then next ten will be 0x201..0x20A, etc. And the page number can jump as index pages, bitmap pages, etc are added to the table. As a result, this technique does not work reliably with no-fragmented tables in OnLine. Since you are using temp tables, it won't be fragmented. If you were using a fragmented table, then you would have created it with the WITH ROWIDS tag. I think that the ROWID behaves more or less like a second SERIAL column and the numbers might be contiguous again. But it isn't safe... If you have version 7.3, then there is syntax available to do this automatically: SELECT FIRST 50 UNIQUE * FROM SomeTable ... > On Fri, 11 Sep 1998, D. Sanderson wrote: > > I want an SELECT statement that returns the first x results (x being a > > constant, say, 50) matching certain criteria. (I want an exact number, so > > I can't use the 'fraction of' formula in the FAQ.) Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn