Unanswered: Limiting with the number of rows in a Query with Offset
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 ?