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

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 ... I assume that you are using this to scroll through data for a user. Showing them 5 records at a time in some order. If that is the case then you can keep track of the max value of the sorting parameter. For example currPageBreak=0 currPageBreakKey=0 Select First 5 mt.*, xyz sort_column from mytable mt where<some-condition or no condition for all of the records> and xyz > currPageBreak and mytable_id > currPageBreakKey order by sort_column, mytable_id The application then processes each record and sets currPageBreak to the last value of the sort_column and currPageBreakKey to the last value of mytable_id. It then can re-execute the query with the above values. I think this is a better way because it doesn't use special non-standard SQL ( except of course the widely implemented first). If you really want to be standard then I think that you can do it with a five or six way nesting (its very icky). select * from mytable mt1 where sort_value <= ( sql to calculate 5thlowest record by sort_value. I will only type it for CASH of course this assumes sort_value is unique which it usually isn't. In that case you would use sql92 handy tuple comparison, which informix doesn't implement but that you can simulate with string concatenation ). By the way there may indeed be a syntax error in your sql. If mycol is not in the select statement, which is required for your version of sql then I think Informix may give you a syntax error. Try this SELECT FIRST 5 mycol, * FROM mytable WHERE rowid > (257-1+5) AND EXISTS (SELECT mycol, * FROM mytable WHERE rowid < (257+10) ORDER BY 1) ORDERBY 1