Using offset in select statements

I need to limit the output of a SELECT query for display purposes, and am looking for some options to "recreate" the "OFFSET" function as used in PostgreSQL. For example, the full select has 100 rows, but in the first query I only want rows 1-20, then 21-40, etc. What are my options to achieve this in a SELECT statement with Informix? ( I am using the Perl DBI and have looked at modules like DBIx::Recordset, but they don't seem to provide a universal OFFSET function.) Thank you -- Tielman de Villiers <tvilliers@lastminute.com> Perl programmer, lastminute.com plc. +44 (20) 7802.4393 voice

Tielman de Villiers wrote: > I need to limit the output of a SELECT query for display purposes, and > am looking for some options to "recreate" the "OFFSET" function as used in > PostgreSQL. > > For example, the full select has 100 rows, but in the first > query I only want rows 1-20, then 21-40, etc. What are my options to > achieve this in a SELECT statement with Informix? > > ( I am using the Perl DBI and have looked at modules like DBIx::Recordset, > but they don't seem to provide a universal OFFSET function.) IDS provides FIRST n (as in SELECT FIRST 100 * FROM WhereEver), and that limits the number of rows returned. If you decide you don't want to use the first 80 of those rows, read and discard them. Or, except in DBI, use a SCROLL cursor. Unfortunately, DBI has a blind spot on the subject of scroll cursors (ask Mr Bunce -- I've requested them on a number of occasions; and they'd be a lot more useful than a number of features that *are* in DBI, too). I suspect that some other DBMS (probably including Oracle) don't provide support for them, hence the lack of interest. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/