Not sure if I misunderstand something here, but if you wnat elements 5
- 10, wy not select the first 10 and discard the first 5?

In order to select elements 5 - 10 the database would need to retrive
the all the first 10 anyway, no matter if the app or the database
passes the result back.

I can understand that you may want to reduce the network traffic by
not passing unneeded rows back to the app, and if thats the case you
could probably write a stored procedure or function to discard the
unwanted rows.




sepp__huber@web.de (sh) wrote in message news:<f0b9f72b.0401270635.51204954@posting.google. com>...
> Hello,
>
> I'm absolutly new to Informix. I have got an application which
> accesses Informix (IBM IDS Version 9.40.UC3) via JDBC.
> I need to limit the number of rows in query with an offset, for
> implementing a simple paging mechanism.
> In MySql it is something like that
>
> select * from mytable limit 5, 5
>
> and 5,5 means from offset 5 , 5 rows.
> In Oracle you can do it with the MINUS operator for two queries:
> select * from mytable WHERE ROWNUM <= (5+5) MINUS select * from
> mytable WHERE ROWNUM <= 5
>
> After researching I found FIRST and ROWID. The bad thing at FIRST is ,
> that you cannot use it in sub-queries
>
> SELECT FIRST 5 * FROM mytable WHERE EXISTS (SELECT FIRST 10 * FROM
> mytable)
>
> so that the query above is not working.
> A combination of rowid and first does the job
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10))
>
> and first I was happy. But if you want add an ORDER BY Clause to that
> statement, Informix gets an syntax error
>
> SELECT FIRST 5 * FROM mytable WHERE rowid > (257-1+5) AND EXISTS
> (SELECT * FROM mytable WHERE rowid < (257+10) ORDER BY mycol) ORDER BY
> mycol
>
> Any ideas ?
> The other problem ist that the ROWID variable does not always start at
> 257, it depends on the table ...