Re: Whatcha' wanta have?????

Richard Harnden wrote: > > Paging through your result-set is a problem for the client - NOT the > database. I'd have to agree with that, yet MS-SQL and a few others (?) offer something like select first N offset O ...... but using their own syntax. I have assumed that they all read the first 2000 rows before giving you your desired 100 rows at offset 2000. However I think I've read that they use some sort of persistence to implement this; I guess it's gambing on the fact that you might call for the next block of rows if you've called for the first. *shrug* Was your comment a statement of a "should" that rejects this feature in the engine, or was it a cryptic statement which says that the client-side of MS engines does half the work?

"Andrew Hamm" <ahamm@mail.com> wrote in message news:c0ov8i$18tm68$1@ID-79573.news.uni-berlin.de... > Richard Harnden wrote: > > > > Paging through your result-set is a problem for the client - NOT the > > database. > > I'd have to agree with that, yet MS-SQL and a few others (?) offer something > like > > select first N offset O ......> > but using their own syntax. I could be wrong, but I think it's something like: ORDER BY <col-list> [LIMIT <num-rows> [OFFSET <num-rows>] ] ie, asking for the first n rows only makes any sense if you order your results. > > I have assumed that they all read the first 2000 rows before giving you your > desired 100 rows at offset 2000. However I think I've read that they use > some sort of persistence to implement this; I guess it's gambing on the fact > that you might call for the next block of rows if you've called for the > first. *shrug* > > Was your comment a statement of a "should" that rejects this feature in the > engine, or was it a cryptic statement which says that the client-side of MS > engines does half the work? That a select statement is supposed to return a relation, and that FIRST, LIMIT-OFFSET (and CONNECT-BY) are operations that require a cursor. That they only pretend to be set-operations. And that you shouldn't expect to be able to do it in sql. Cryptically, that sql should evolve towards Tutorial-D, not away from it. I'd guess that this kind of functionality is most often requested by people writing search forms for web pages, which makes persistence a problem. But it's a problem for cgi, however inconvenient it might be. Of course, if every other database vendor provides this functionality then Informix doesn't really have any choice but to do so as well.

Richard Harnden wrote: > "Andrew Hamm" <ahamm@mail.com> wrote in message > news:c0ov8i$18tm68$1@ID-79573.news.uni-berlin.de... > >>Richard Harnden wrote: >> >>>Paging through your result-set is a problem for the client - NOT the >>>database. >> >>I'd have to agree with that, yet MS-SQL and a few others (?) offer > > something > >>like >> >>select first N offset O ......>> >>but using their own syntax. > > > I could be wrong, but I think it's something like: > ORDER BY <col-list> [LIMIT <num-rows> [OFFSET <num-rows>] ] > > ie, asking for the first n rows only makes any sense if you order your > results. > > >>I have assumed that they all read the first 2000 rows before giving you > > your > >>desired 100 rows at offset 2000. However I think I've read that they use >>some sort of persistence to implement this; I guess it's gambing on the > > fact > >>that you might call for the next block of rows if you've called for the >>first. *shrug* >> >>Was your comment a statement of a "should" that rejects this feature in > > the > >>engine, or was it a cryptic statement which says that the client-side of > > MS > >>engines does half the work? > > > That a select statement is supposed to return a relation, and that FIRST, > LIMIT-OFFSET (and CONNECT-BY) are operations that require a cursor. That > they only pretend to be set-operations. And that you shouldn't expect to be > able to do it in sql. > > Cryptically, that sql should evolve towards Tutorial-D, not away from it. > > I'd guess that this kind of functionality is most often requested by people > writing search forms for web pages, which makes persistence a problem. But > it's a problem for cgi, however inconvenient it might be. > > Of course, if every other database vendor provides this functionality then > Informix doesn't really have any choice but to do so as well. > > I think for this requirement scrollable cursors are the right answer. What happens there is that the resultset gets materialized on the server and the client can then browse through it by position. This allows the server to only materialzie to the current high watermark, without having to redo the work for the next request, as it would have to do with a pure SQL solution which would resubmit similar statements over and over again. (Scrollable cursor scan do other nifty things like optimistic locked etc..) Within the realms of normal SQL I think the standard provides FETCH FIRST n ROWS ONLY. I haven't heard of any offset clause. But all this is really syntactic sugar around the row_number() over() OLAP function that can be used to filter whatever you please.. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab