I found that SELECT * FROM foo ORDER BY bar OFFSET n LIMIT m and SELECT * FROM foo ORDER BY bar LIMIT m OFFSET n produce always same results. Why ? OFFSET and LIMIT operations are NOT commutative in general. Andrus.

> 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. > <http://www.postgresql.org/docs/8.2/interactive/queries-limit.html> >> If a limit count is given, no more than that many rows will be returned >> (but possibly less, if the query itself yields less rows). > ... >> OFFSET says to skip that many rows before beginning to return rows. > > Why would the position of either clause matter, assuming the clause is in > a legal position? > In both your examples, the LIMIT is 'm', so you will get 'm' rows. > > In both your examples, the OFFSET is 'n', so you will skip 'n' rows before > returning those 'm' rows. > > I see no inconsistency. Different results - the first gives results (zero-based) n to n+m-1. The second gives results 0 to Min(n-1, m-1). > Also, neither LIMIT nor OFFSET is a binary operator, so the term > "commutative" has to be understood metaphorically at best. What exactly > do you mean by "commutative"? I meant result should depend on the order of OFFSET and LIMIT clauses are present in SELECT clause. SELECT ... OFFSET n LIMIT m SELECT ... LIMIT m OFFSET n should return different results in sime cases. Filtering and ordering are effectively orthogonal. LIMIT and OFFSET are clearly *not* orthogonal. Otherwise I see no way to implement efficient LinQ-PostgreSQL driver. Andrus.

> That's SQL, my friend. OFFSET first, then LIMIT. Irrespective of the > order > in the query statement. It is what it is. SQL doesn't depend on LINQ for > its semantics. SQL requires strong order of all cases. Postgres syntax help about OFFSET / LIMIT also describes only single order. Actually Postgres allows order of OFFSET / LIMIT clauses swapped without any error. Why Postgres does not throw error when SELECT ... LIMIT ... OFFSET is used ? That's not sql and should cause error. Andrus.