How to find the previous and next record using a single query in MySQL?

mysql, nearest-neighbor

Solution

You need to change up your `ORDER BY`:

SELECT * FROM table WHERE `id` > 1556 ORDER BY `id` ASC LIMIT 1
UNION 
SELECT * FROM table WHERE `id` < 1556 ORDER BY `id` DESC LIMIT 1

This ensures that the `id` field is in the correct order before taking the top result.

You can also use MIN and MAX:

SELECT
    * 
FROM
    table 
WHERE 
    id = (SELECT MIN(id) FROM table where id > 1556) 
    OR id = (SELECT MAX(id) FROM table where id < 1556)

It should be noted that `SELECT *` is not recommended to have in production code, though, so name your columns in your `SELECT` statement.

Problem

I have a database, and I want to find out the previous and next record ordered by ID, using a single query. I tried to do a union but that does not work. :( ``` SELECT * FROM table WHERE `id` > 1556 LIMIT 1 UNION SELECT * FROM table WHERE `id` <1556 ORDER BY `product_id` LIMIT 1 ``` Any ideas? Thanks a lot.

Original source