CODEWITHSUNDEEP
sqlmysql
bobbyh
bobbyh

Reputation: 643

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

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? :-)

Upvotes: 9

Views: 13154

Answers (4)

FerranB
FerranB

Reputation: 36827

By definition the data on a RDBMS table are not ordered. It means that when you run a query, if you don't specify an ORDER BY clause, the order can be different from one query to another. Furthermore, you have to consider that the next query is going to (or, at least, may) return a different order.

Suppose that may be another user on database inserts a new record and the RDBMS considers to put it between the rows you are considering. Or the DBA is moving the table to another in online basis (this is possible in Oracle, for instance), in this case the data can be changed on any matter.

For your question, you have to build a kind of query result cache to determine prior and next rows on the queries.

Upvotes: 1

cdonner
cdonner

Reputation: 37698

Look at this older question as well.

My guess is that the UNION with LIMIT 1 performs better than the aggregates in my answer here.

Upvotes: 2

cdonner
cdonner

Reputation: 37698

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)

Upvotes: 15

dkretz
dkretz

Reputation: 37655

Here's a subtle but efficient way to accomplish this, and it works on even old versions of MySQL which don't support subqueries --

SELECT *    
FROM bb_topics AS bb1    
LEFT JOIN bb_topics AS bb2    
    ON bb1.topic_poster = bb2.topic_poster    
    AND bb2.topic_id > bb1.topic_id    
LEFT JOIN bb_topics AS bb3  
    ON bb1.topic_poster = bb3.topic_poster    
    AND bb3.topic_id > bb1.topic_id    
    AND bb3.topic_id < bb2.topic_id  
WHERE bb1.topic_poster = 5  
    AND bb3.topic_id IS NULL

This gets you the next adjacent post. I can fill in the symmetric clauses for the next previous post if it's not self-evident.

EDIT - minor corrected alii.

Upvotes: 0

Related Questions