SQL: Limiting with the number of rows in a Query with Offset

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 * frommytable 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 BYmycol Any ideas ? The other problem ist that the ROWID variable does not always start at 257, it depends on the table ...

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 ...

It's entirely meaningless to attempt to use FIRST in this way because you cannot guarantee the order in which the rows are stored or will be returned. Similarly with rowid, which has the added complication that you cannot assume the numbers will be consecutive. Basically you need to be testing values of things, not some arbitrary position in the physical storage. You are making fundamental misunderstandings about how SQL, rather than Informix, works. Andy 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 ...