Re: Why LIMIT and OFFSET are commutative

On Nov 26, 2007, at 5:29 AM, Andrus wrote:

>> Under what interpretation would the results differ?
>
> Results must differ for easy creation of LinQ-PostgreSQL driver.
> If results are always the same , PostgreSQL should not allow to use
> both
> order of clauses.
>
> Nicholas explains:
>
>    Assuming the ordering is the same on each of them (because Skip
> and Take
> make no sense without ordering, LINQ to SQL will create an order
> for you,
> which irritates me to no end, but that's a separate thread), they will
> produce different results.
>
>     Say your query will produce the ordered set {1, 2, 3}.  Let n =
> 1, m =
> 2.
>
>     The first query:
>
> var query = query.Skip(n).Take(m);
>
> converted to SELECT ... OFFSET n LIMIT m
>
>     Will return the ordered set {2, 3}, while the second query:
>
> var query = query.Take(m).Skip(n);
>
> converted to SELECT ... LIMIT m OFFSET n
>
>     Will return the ordered set {2}.
>
>     The reason for this is that in the first query, the Skip method
> skips
> one element, then takes the remaining two, while in the second
> query, the
> first two elements are taken, and then the first one is skipped.

Nice.  Yet another example of an Object-Relational impedance
mismatch.  SQL is declarative, not procedural.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com