Oct
30

MySQL – Select Previous/Next Entries

vjeux MySQL 2009-10-30

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.
 
All Posts
«
»
 

Random Posts

  • October 30, 2009 -- Javascript – Dynamic Query Throttling (1)
    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 server on field...
  • December 7, 2011 -- C++: Fuzzy Search with Trie (0)
    For a school project, I had to make a part of a spell-check program. Given a dictionnary of words, you have to determine all the words that are within K mistakes of the original word. Trie As input, we've got a list of words along with their frequency. For example, with the following list, we ar...
  • October 12, 2011 -- Hook document.CreateElement (3)
    For a project I will talk later on, I need to hook the function document.createElement. The code I wanted to write was: var original = document.createElement; document.createElement = function (tag) { // Do something return original(tag); }; Problem However, there's a silly Javasc...
  • October 12, 2011 -- Intercept and alter <script> include (0)
    For a project, I want to transparently be able to intercept all the included javascript files, edit the AST and eval it. This way I can manipulate all the code of an application just by inserting a custom script. Hook the <script> tag insertion. Download the Javascript file using XHR. P...
  • August 4, 2009 -- Project – Shortest Path (0)
    A school project was to find the shortest path in a dungeon graph. You start with an amount of hit points, and each edge gives or removes hit points. You have to find the path from two points going through the minimum of edges (no matter their value) alive (hp > 0 all along the path). The difficulty...