RE: howto use ROWNUM which is using in ORACLE??

In my limited Oracle experience, rownum was used to limit the result set from a query to small number of rows. With Informix (IDS 7.3x) this can be accomplished with "SELECT FIRST <n> .... " syntax. -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Tuesday, May 02, 2000 07:28 To: informix-list@iiug.org Subject: Re: howto use ROWNUM which is using in ORACLE?? batti wrote: > > ..I want to know how to use ROWNUM or similar thing in Informix... > > In manual site, I already figure out that informix doesn't have ROWNUM ... > > .Isn't there another way..?? You can use ROWID, the equivalent hidden internal identifier for a row. However, there are several gotchas with rowids: o ROWID for non-fragmented tables are the physical address of the row within the table representing the page number the row resides on and its slot number (relative order) on the page. If you unload and reload a table or otherwise reorganize the table the rowid of a particular row WILL change so you CANNOT use rowid as a foreign key in a related table. Also, unlike Oracle ROWNUM, ROWID has nothing to do with the order in which rows were added to the table. o Fragmented tables do NOT have ROWID unless the table was EXPLICITELY created with rowids included (the WITH ROWID clause) which actually adds an indexed unique column to the table which is not normally returned. Again reorganizing the table will change the ROWID hidden column value. It is NOT recommended to use rowids in queries except in VERY short term sequences of operations, and even then the practice is discouraged. Why don't you tell us what it is you would use Oracle ROWNUM for in your application and we will recommend the Informix paradigm for how to best accomplish this. Art S. Kagel