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:

1a. If the SQL query contains an ORDER BY clause, run the query, sort the
results and return the first n results from a sorted result set.
(I don't remember right now, but if the GROUP BY (column list) clause always
forces the engine to sort the results by (column list) then GROUP BY should
work exactly the same way)

1b. If the SQL query does not contain an ORDER BY clause, simply return the
first n rows from the result set. Developers should be aware that there is
no inherent row order so the same query with FIRST n clause could return
different results when ran twice even if no data was changed.

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.

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.

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.

Can we agree on this one?

Sincerely,

Syarzhuk
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com