Re: Why LIMIT and OFFSET are commutative

From: Erik Jones <erik(at)myemma(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why LIMIT and OFFSET are commutative
Date: 2007-11-27 17:23:06
Message-ID: 3F66E01E-D2FF-4C3F-AED9-4F7D1001AB22@myemma.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)myemma(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2007-11-27 17:26:04 Re: Non-unique values problem after 'add column'
Previous Message Tom Lane 2007-11-27 17:19:51 Re: [GENERAL] Empty arrays with ARRAY[]