Skip site navigation (1) Skip section navigation (2)

grabbing id of previous and next record for current select

From: "Alan T(dot) Miller" <amiller(at)onlinebrands(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: grabbing id of previous and next record for current select
Date: 2007-01-11 21:17:35
Message-ID: 45A6A96F.9060305@onlinebrands.com (view raw or flat)
Thread:
Lists: pgsql-novice
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)

HOWEVER:

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 
the formula:

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 
smallest ID.

Any ideas? Anyone?

Would appreciate the help.

Alan

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2007-01-11 22:10:01
Subject: Re: grabbing id of previous and next record for current select
Previous:From: Tom LaneDate: 2007-01-11 20:26:32
Subject: Re: Problem with FOR LOOP query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group