MySQL - Select Previous and Next Row

  • By Ryan
  • 08:39 pm, Nov 18, 2010

I was faced with a problem the other day in which I needed to select the previous and next row in a MySQL database.  This can easily done if you are using an auto-increment field as the condition.  Unfortunately, the results need to be sorted with 2 different conditions, making the auto-increment field useless.

One thing I didn't want to do was use server-side programming to loop through the full dataset.  This will work fine for smaller datasets, but I'm after scalability and performance.

I wanted to accomplish this in a single query, but was unable to figure out a way to do this.  I ended getting my results back in 2 queries.  Here's how I did it...

First we need the to get the position for the current id

SET @num = 0;

SELECT `position`
FROM 
( SELECT @num := @num + 1 AS 'position'
	FROM `table`
	ORDER BY `sort`, `date_created`
) AS subselect 
WHERE `id` = $id
ORDER BY position DESC
LIMIT 1;

The inner select uses the variable @num to create an incremented value for each row and sorts by the sort column and the date created column.  The outer select then takes the inner select results, sorts by position, and retreives the position of the passed id.  Limit 1 is not necessary, but is there for reference.

Now that we have the position of the passed id, we can requery the same inner select to find position - 1 and position + 1

SET @num = 0;

SELECT `id`
FROM 
( SELECT @num := @num + 1 AS 'position', `id`
	FROM `table`
	ORDER BY `sort`, `date_created`
) AS subselect 
WHERE `position` IN ($position - 1, $position + 1)
ORDER BY position DESC;

This was the best I could come up with and it seems to work fine. Do let me know if you have a better solution.

1 Response to "MySQL - Select Previous and Next Row"

  1. Mike January 3, 2012 at 3:32 pm
    Here's what I came up with for a similar problem:
        SELECT id
    FROM posts
    WHERE time >= (SELECT MAX(time)
        FROM posts
        WHERE time < (SELECT time
            FROM posts
            WHERE id = $id))
    ORDER BY time ASC
    LIMIT 3
    With one query that returns three values (or two if its the last row): last, current and next. It only fails if the value of id is the first row. If you get an empty return value, just execute the following to get the id of the second row:
        SELECT id
    FROM posts
    WHERE time = (SELECT MIN(time)
        FROM posts
        WHERE time >
            (SELECT time
            FROM posts
            WHERE id = $id))
    Reply
Leave a Comment
Not made public
To leave markup, use tab or 4 space indention

Recent Articles

  • PHP Memoization

    One of my favorite optimization techniques is memoization. Memoization is a technique used to store ... Read More