In SQL, this is a common issue to query the previous and next entries of the database. In order to achieve this, we are going to use a table that is sorted by the `id` field. To get the previous, the technique consists in sorting all the fields that are lower that the current one and taking the first one.

SELECT *
FROM `table`
WHERE `id` < $current_id
ORDER BY `id` DESC
LIMIT 1

Let's take an example with the values [8, 3, 4, 9, 6, 1]. We want to get the previous of 8.

  • WHERE `id` < $current_id
    We first take all the values strictly lower than 8
    Result: [3, 4, 1, 6]
  • ORDER BY `id` DESC
    We then sort them in descending order
    Result: [6, 4, 3, 1]
  • LIMIT 1
    We take the first one
    Result: 6

You can apply the same method for the next entry:

SELECT *
FROM `table`
WHERE `id` > $current_id
ORDER BY `id` ASC
LIMIT 1
If you liked this article, you might be interested in my Twitter feed as well.
 
 

Related Posts

  • October 8, 2011 Copy SQL Row Changing ID (3)
    I've come across an SQL issue. I need to make a fake spell for the WoW database. However creating one from scratch is too annoying, there are like 30 non-NULL values to fill. Instead what I want is to copy an existing spell with the new id. It appeared to be less trivial than […]
  • October 30, 2009 Javascript – Dynamic Query Throttling (3)
    Working on the World of Raids Recruitment Tool we wanted automatic saving while editing. There are basically two ways of handling the problem. Send update everytime something changes. The main advantage of this technique is that you are always up to date. However, it is spamming the […]
  • September 13, 2015 Fixing a 10 years old performance issue (6)
    When I was 14, Philippe Pelletier reached out to me with an interesting project. He is passionate about cinema and maintains a database (lots of physical sheets of paper and a growing number of Word documents) of every movie an actor played in, the cover of the movie, a biography of that […]
  • August 23, 2011 Javascript – Hook Technique (7)
    Let's go back 5 years ago during the World of Warcraft beta. I was working on Cosmos UI, a projects that aimed to improve the World of Warcraft interface. As interface modification was not officially supported by Blizzard, we went ahead and directly modify the game files written in […]
  • September 24, 2011 Javascript: Cyclic Object Detection (15)
    URLON.stringify() suffer from a problem, when passed an object that contains a cycle, it will never stop. This article shows 3 techniques in order to detect if an object is cyclical. Edit the object: Mark In order to detect a cycle in an object, the method we learn at school is to […]