Re: SELECT * FROM foo OFFSET -1 LIMIT 1 - Mailing list pgsql-sql

From Markus Bertheau
Subject Re: SELECT * FROM foo OFFSET -1 LIMIT 1
Date
Msg-id 1119897697.7464.7.camel@localhost.localdomain
Whole thread Raw
In response to Re: SELECT * FROM foo OFFSET -1 LIMIT 1  (Martín Marqués <martin@bugs.unl.edu.ar>)
Responses Re: SELECT * FROM foo OFFSET -1 LIMIT 1
List pgsql-sql
Dnia 27-06-2005, Mo o godzinie 14:17 -0300, Martín Marqués napisał(a):

> How do you skip -1 rows?

You redefine OFFSET to mean "where in the result set to start returning
rows" and give -1 the meaning that I described above.

Please don't ask me, where in the result set position -1 is.


I've stumbled over this because I needed a query which given an id a
result set like this:

id txt pos
23 foo 0
11 bar 1
34 baz 2
13 xop 3

returns the previous and next rows relative to the given id, as ordered
by the position). That would be 23 and 34 for 11, or NULL and 11 for 23.
I'm basically determining the position of the middle row and then
querying with offset pos-1 limit 1 and offset pos+1 limit 1.

Offset for negative numbers means 0, as it seems. I think there is a
sensible meaning for negative offset numbers and wondered, what
arguments led to negative offsets being processed as 0 offset.

Markus

--
Markus Bertheau <twanger@bluetwanger.de>

pgsql-sql by date:

Previous
From: Martín Marqués
Date:
Subject: Re: SELECT * FROM foo OFFSET -1 LIMIT 1
Next
From: Greg Stark
Date:
Subject: Re: SELECT * FROM foo OFFSET -1 LIMIT 1