SQL - find next and previous rows given a particular WHERE clause

Question

I have a MySQL table called bb_posts used by a bbPress forum. It has an autoincrement field called topid_id and another field called topic_poster.

I'm trying to write a function that finds the "next post by the same author". So, for instance, say the user is on a particular page that displays topic 123. If you do a SQL query:

SELECT *
FROM `bb_topics`
WHERE `topic_poster` = 5
ORDER BY `topic_id` ASC

This might return the following rows:

topic_id    topic_poster
6           5
50          5
123         5
199         5
2039        5

What I'd like to do is write a SQL query that returns these two rows:

topic_id    topic_poster
50          5
199         5

This would be the row PRIOR to the row with topic_id of 123, and the row AFTER that row.

If it's too hard to do this in one query, it's definitely OK to break this up into two queries...

I'd like to avoid doing the whole SQL query ("SELECT * FROM bb_topics WHERE topic_poster = 5") and looping through the results, because the result set is sometimes huge.

Is this possible? :-)

Answer 1

Next one:

SELECT * FROM `bb_topics` 
      WHERE `topic_id` = 
      (select min(`topic_id`) FROM `bb_topics` where `topic_id` > 123
         and `topic_poster` = 5)

Previous one:

SELECT * FROM `bb_topics` 
      WHERE `topic_id` = 
      (select max(`topic_id`) FROM `bb_topics` where `topic_id` < 123
         and `topic_poster` = 5)

Both:

SELECT * FROM `bb_topics` 
      WHERE `topic_id` = 
      (select min(`topic_id`) FROM `bb_topics` where `topic_id` > 123
                     and `topic_poster` = 5)
      or `topic_id` = 
      (select max(`topic_id`) FROM `bb_topics` where `topic_id` < 123
                        and `topic_poster` = 5)