Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: 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 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 ?

  2. #2
    Join Date
    Jan 2004
    Posts
    2
    Anybody out there who knows a solution ?
    Last edited by sepp__huber; 01-27-04 at 10:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •