For me, LIMIT M TO N would be _perfect_. I can live without the ROWNUM.

However, the sub-range selection should be applied AFTER the sorting for
it to be useful. (*)


Thank you very much for your attention,

Paulo Gaspar

(*) - One can make it work that way with queries over queries in Oracle.

> -----Original Message-----
> From: Syarzhuk Kazachenka [mailto:bamboo7431@...]
> Sent: Thursday, June 07, 2001 4:06 AM
>
>
> 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