I seem to remember the late, lamented R:Base having a syntax

SELECT *
FROM ...
WHERE .... AND COUNT < 10 (or COUNT BETWEEN 11 AND 20, etc)

Would this solve the problem? Live data for the rows just fetched, but
the query run repeatedly on the server? Seems like a lot of extras
server work, but would do the job.

ed

ft@... wrote:

>
> Hi Syarzhuk
>
> > >It seems the only use of FIRST n or TOP n is in interactive queries
> > >during development. You want to see quickly if your query returns
> any rows
> > >at all, or examine the formatting of returned columns.
> > Not true. I can think of at least two more examples:
> > 1) Suppose I'm using a full-text search add-on and it gives me a
> calculated
> > column with estimated relevancy of the result (from 0 to 100). I
> might just
> > say "order by relevancy descending and give the user first 20
> records,
> > because others are probably not relevant to her search":
> > SELECT FIRST 20 * FROM TableName ORDER BY Relevancy(UserSearchStr)
> DESC
>
> I did not mean that you don't often fetch the first n records in
> applications. For this you can issue the query and fetch only the
> first 10 rows. In interactive console work however, you use a FIRST n
> keyword so that the result set does not scroll.
>
> > >One could make use of ROWNUM as described above to issue one query
> to
> > >return the first row of each page that would be returned in chunks.
> > Yes, but this way you would be putting more logic in the
> application and
> > less in SQL. With the LIMIT you could just request a page at a time
>
> What I meant was something like:
>
> SELECT ........ WHERE ROWNUM(modulo 20 equals 1)
>
> (NB: This is pseudocode, the syntax of ROWNUM should be worked out)
>
> To fetch the result set for the page headings, which would look like:
>
> Alex, [phone], [address], [id}, ....
> Bob, [phone], [address], [id}, ....
> Ed, [phone], [address], [id}, ....
> Gill, [phone], [address], [id}, ....
> ..
>
> I could also add calculated columns based on ROWNUM to the query to
> produce the 21-40, 41-60, ... sequences but this is not the main
> issue.
>
> Also, I could add to the above query:
>
> .. OR (ROWNUM BETWEEN M, N)
>
> to include full results for the current page
>
> Alex, [phone], [address], [id}, ....
> Bob, [phone], [address], [id}, ....
> Ed, [phone], [address], [id}, ....
> Edith, [phone], [address], [id}, ....
> Edwin, [phone], [address], [id}, ....
> Elle, [phone], [address], [id}, ....
> Elsa, [phone], [address], [id}, ....
> Esther, [phone], [address], [id}, ....
>
> . more rows for the page between Ed and Gill....
>
> Gill, [phone], [address], [id}, ....
> ..
>
> ROWNUM is a more general concept than LIMIT. Because ROWNUM is needed
> in order to implement LIMIT, it may as well become accessible within
> queries to allow more felxibilty with fewer queries.
>
> Fred Toussi
>
> Community email addresses:
> Post message: IBDI@yahoogroups.com
> Subscribe: IBDI-subscribe@yahoogroups.com
> Unsubscribe: IBDI-unsubscribe@yahoogroups.com
> List owner: IBDI-owner@yahoogroups.com
>
> Shortcut URL to this page:
> http://www.yahoogroups.com/community/IBDI
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/