Selecting Previous & Next Record based off Ordering column

PostgreSQL Help

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

April 24th, 2008, 06:49 PM

madchild

Contributing User

Join Date: Jun 2004

Posts: 149

Time spent in forums: 2 Days 13 h 44 m 30 sec
Reputation Power: 5

Selecting Previous & Next Record based off Ordering column

I'm trying to select the ID of the previous and next record based off of an column that contains the ORDER. I pass the SELECT statement an ID of a record and it is suppose to return the previous record ID and the next record ID. The SELECT statement I'm current using works in most cases, but it is flawed and I'm not seeing why. Any help would be appreciated.

PHP Code:


		
			
 $sql = "SELECT id,

   (SELECT MAX(id) FROM content AS ma WHERE ma.ordering < content.ordering) AS prev_row,

   (SELECT MIN(id) FROM content AS mi WHERE mi.ordering > content.ordering) AS next_row 

                FROM content WHERE id = " . $db->qstr($id);

April 24th, 2008, 08:07 PM

madchild

Contributing User

Join Date: Jun 2004

Posts: 149

Time spent in forums: 2 Days 13 h 44 m 30 sec
Reputation Power: 5

I got it working fine with 3 queries, but there seems like a more efficient way of doing this.

PHP Code:


		
			
 $cur_id_order = $db->getRow("SELECT ordering FROM content WHERE id = " . $db->qstr($id));



$prev_id = $db->getRow("SELECT id as prev_id FROM content WHERE ordering < " . $db->qstr($cur_id_order->ordering) . " ORDER BY ordering DESC LIMIT 1");



$next_id = $db->getRow("SELECT id as next_id FROM content WHERE ordering > " . $db->qstr($cur_id_order->ordering) . " ORDER BY ordering LIMIT 1");

Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Selecting Previous & Next Record based off Ordering column

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump