Let’s assume we have selected a record (record_id = 7) and we want to select its previous and next record.
A very naive approach would be to add “1” (and to subtract “1”) to the record_id:
SELECT * FROM table WHERE record_id = (7-1) # selects previous record
SELECT * FROM table WHERE record_id = (7+1) # selects next record
However, this does not work in many scenarios, e.g., if record_id is not a number or if numbers are “missing” (…,4,5,7,8,…). Therefore, a more sophisticated query is needed.
One possibility is to explicitly sort the records according to the record_id, add a condition which selects only records that have a record_id that is greater (or less) than the record_id of the selected record, and finally select only one record:
SELECT * FROM table WHERE record_id < 7 ORDER BY record_id DESC LIMIT 1 # selects previous record
SELECT * FROM table WHERE record_id > 7 ORDER BY record_id ASC LIMIT 1 # selects next record
