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.