Re: SELECTing first x results (a FAQ?)

In article <6tjfep$q1k$1@news.xmission.com>, Jonathan Leffler <jleffler@informix.com> writes > >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... > Agreed, this is why it is not the FAQ. I'm not putting in one in from SQL for Smarties because it looks too slow. Why can't you just have the application fetch the first N rows and stop there? >If you have version 7.3, then there is syntax available to do >this automatically: > > SELECT FIRST 50 UNIQUE * FROM SomeTable ...> This sounds even better, let the engine do what the app would do!! One more for the FAQ. PS Now I've finally found somewhere else too live + got the phone connected I'll be updating the FAQ.. >> 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 > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care