--- In IBDI@y..., "Syarzhuk Kazachenka" <bamboo7431@h...> wrote:

> After reading all these threads I think here's what the discussion

is coming

> to:
> 1. It would be nice if we could add a FIRST n or TOP n (I don't

care what

> it's called) extension to the IB/FB SQL. It should do the following:

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.

> Now to the question about Web users paging thru results:
> 2. It would be nice if we could an extension to SQL that would

allow to

> select only rows M to N from an *ordered* list. Again, I don't care

how it's

> called - LIMIT M TO N or WHERE ROWNUM BETWEEN M AND N.
>
> 2a. If the SQL query contains a predicate that forces the engine to

sort the

> result set (ORDER BY or GROUP BY) then the engine should run a

query, sort

> the result set and only return rows M to N from the result set.
>
> 2b. If the SQL query doesn't force the engine to sort the result

set, I

> could agree with either of the two approaches:
>
> 2bE - returning an Error that the result set is not sortable;
>
> or
>
> 2bR - returning only Records M to N from the original unsorted

result set;

> generally speaking, such behavior will cause unpredictable return

results

> and will be of little to no use.

Both options are fine. But in addition, it has been propesed that a
ROWNUM psuedocolumn becomes available within the scope of compound
queries. The rows of the result set returned by any query would be
numbered from 1 to rowcount and this number can be used in a main
query or a subquery to return a subset of the original result set.
(This extends the current behavious of Oracle ROWNUM).

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.
If I were dividing an alphabetically sorted list of names and
addresses into 20 pages of 20 entries each, the hyperlinks could
display the first name that would appear on each page alongside its
record range.

1-20 Alex 21-40 Bob 41-60 Ed 61-80 Gill ...

> As far as I understand, proposal 1 is easy to implement right now

(but do we

> all agree that limiting resultset to the first N records should be

happening

> AFTER the sort? I know Oracle would assign ROWNUMs first, limit the

result

> set (to, say, first 100 rows if you used ROWNUM<=100) and only

*then*

> applying the sort) I know sorting the whole set is expensive, but

there is

> no free lunch.

I can't see what use the Oracle behavious has apart from being a
development aid. Suppose the main sort key is people's names. The 100
records could be a mix or could contain only names beginning with D
or with Z.

> If proposal 1 is implemented, I think proposal 2a will the next

logical

> step, after all LIMIT M TO N == FIRST N - FIRST M.
> I think we could stop at 2bE (returning an error if a LIMIT query

is not

> sorted), and 98% of the developers will be happy.

I would certainly prefer to do selection, filtering and sorting in
SQL, rather than procedural code, and the LIMIT keyword would help a
lot.

Fred Toussi