Обсуждение: Next and previous sequence values
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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. Best Regards, Christopher Nehren -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (FreeBSD) iD8DBQFCN1p8k/lo7zvzJioRAuK7AJ475RloH+MyQ+BDq6a5STvqiTkzhgCeMhc5 G7RKiBriNAR/JugrxEy3yKI= =U1Z1 -----END PGP SIGNATURE----- -- I abhor a system designed for the "user", if that word is a coded pejorative meaning "stupid and unsophisticated". -- Ken Thompson If you ask the wrong questions, you get answers like "42" and "God". Unix is user friendly. However, it isn't idiot friendly.
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/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2005-03-15, Michael Fuhr scribbled these curious markings: >> 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. Yes, that is precisely what I want, and stated quite succinctly. Many thanks indeed. Best Regards, Christopher Nehren -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (FreeBSD) iD8DBQFCN44Nk/lo7zvzJioRAvSDAJ4vFcNmHnrBJ32T+ljF/zb7kovY/ACgvptv Afs5KTTEobI9UjY5vVQDwuA= =PcHW -----END PGP SIGNATURE----- -- I abhor a system designed for the "user", if that word is a coded pejorative meaning "stupid and unsophisticated". -- Ken Thompson If you ask the wrong questions, you get answers like "42" and "God". Unix is user friendly. However, it isn't idiot friendly.