Imagine you have a sequentially indexed table (just think any table with
an id field). When you select a record, you want to at the same time
grab the id of the following record, as well as the id of the previous
record. For the most part you would most likely assume this is simple
enough with the formula being:
id_current + 1 = id_next (would yield the next record id)
id_current - 1 = id_previous (would yield you the previous record id)
The above formula does not work when there are records missing out of
the middle. For example... if you were to have 10 records, numbered 1
through 10 but there was no record 7 for example. If you were trying to
get the previous and next id's for the record 8, you could not rely on
id_current - 1 = id_previous
I know a kludge to get the next id, is to simply use the offset function
in a second SQL statement in the following manner:
SELECT id FROM table ORDER BY id ASC LIMIT 1 OFFSET ':current_id';
but there is no clear way I can think of at this time to get the
previous records id.
Basically I am writing an application that grabs a record from the
database and I want to at the same time grab the id's of the previous
record and the next record to send to my application to form the HTML etc.
The ideal solution would be the ability to issue one query and get all
three results. Also, assuming I want the option of traversing these
records from smallest ID to largest ID, and from the largest ID to the
Any ideas? Anyone?
Would appreciate the help.
pgsql-novice by date
|Next:||From: Michael Glaesemann||Date: 2007-01-11 22:10:01|
|Subject: Re: grabbing id of previous and next record for current select|
|Previous:||From: Tom Lane||Date: 2007-01-11 20:26:32|
|Subject: Re: Problem with FOR LOOP query |