Re: Next and previous sequence values

On Tue, Mar 15, 2005 at 09:57:05PM +0000, Christopher Nehren wrote:

> I have a table whose rows are distinguished by a sequence. I'd like to
> be able to, given a certain value of that sequence, retrieve the next
> and previous values in relation to the given value. Couldn't find
> anything in the docs for the version that I'm running, 8.0. Thanks in
> advance.

Maybe this example will help:

CREATE TABLE foo (id integer PRIMARY KEY);
INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id) VALUES (2);
INSERT INTO foo (id) VALUES (5);
INSERT INTO foo (id) VALUES (6);
INSERT INTO foo (id) VALUES (7);

SELECT id FROM foo WHERE id < 5 ORDER BY id DESC LIMIT 1;
 id
----
  2
(1 row)

SELECT id FROM foo WHERE id > 5 ORDER BY id LIMIT 1;
 id
----
  6
(1 row)

Is that what you're after?  If not then please give an example of
what you'd like to do.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/