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

mysqlnearest-neighbor

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.

Best 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.

Related Solutions

Mysql – How to find all the tables in MySQL with specific column names in them

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

Mysql – How to output MySQL query results in CSV format

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

Best Solution

Related Solutions

Mysql – How to find all the tables in MySQL with specific column names in them

Mysql – How to output MySQL query results in CSV format

Related Question