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