How to get next/previous record in MySQL?

Retrieving the next or previous record in MySQL typically relies on a sorting criterion, such as an ID or date field. Here, I'll provide two examples: one for retrieving records in ID order, and another for time-based ordering.

Example 1: Retrieving Records Based on ID

Suppose you have a table named employees with an id field, and you want to retrieve the next and previous records based on a given ID.

Retrieving the Next Record

sql
SELECT *
FROM employees
WHERE id > given_id
ORDER BY id ASC
LIMIT 1;

Here, given_id is the ID of the current record. This SQL query first identifies all records with id greater than the current given_id, sorts them in ascending order by id, and then returns the first record.

Retrieving the Previous Record

sql
SELECT *
FROM employees
WHERE id < given_id
ORDER BY id DESC
LIMIT 1;

Here, we identify all records with id less than the given given_id, sort them in descending order by id, and then return the first record, which corresponds to the closest smaller value to the given ID.

Example 2: Retrieving Records Based on Timestamp

Consider a table named log_entries with a timestamp field, and you want to retrieve the next and previous records based on a given timestamp.

Retrieving the Next Record

sql
SELECT *
FROM log_entries
WHERE timestamp > given_timestamp
ORDER BY timestamp ASC
LIMIT 1;

Here, given_timestamp is the timestamp of the current record. After sorting in ascending order by timestamp, it selects the first record with a timestamp greater than given_timestamp.

Retrieving the Previous Record

sql
SELECT *
FROM log_entries
WHERE timestamp < given_timestamp
ORDER BY timestamp DESC
LIMIT 1;

This query identifies all records with timestamps less than given_timestamp, sorts them in descending order by timestamp, and then returns the first record.

Important Considerations

The efficiency of this method depends on indexing the fields. If fields like id or timestamp are not indexed, these queries may execute slowly. Therefore, in practice, ensure that the fields used for sorting and comparison are indexed.

These examples demonstrate how to retrieve adjacent records in a database based on different sorting criteria. This technique is particularly useful for implementing pagination or browsing specific records.