Hello,

I'm absolutly new to Informix. I have got an application which accesses Informix 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.
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, perhaps without doing it with ROWID ?
Does ROWID always start at 257 ?

Anybody out there who knows a solution ?