Re: grabbing id of previous and next record for current select

On Jan 12, 2007, at 6:17 , Alan T. Miller wrote:

> When you select a record, you want to at the same time grab the id
> of the following record, as well as the id of the previous record.

> I know a kludge to get the next id, is to simply use the offset
> function in a second SQL statement in the following manner:
>
> SELECT id FROM table ORDER BY id ASC LIMIT 1 OFFSET ':current_id';

This won't necessarily work: offset is based on the number of rows,
not their id.

> The ideal solution would be the ability to issue one query and get
> all three results. Also, assuming I want the option of traversing
> these records from smallest ID to largest ID, and from the largest
> ID to the smallest ID.

Here's what I'd try:

SELECT *
FROM (
-- record with previous id, if exists
SELECT *
FROM foo
WHERE id < :current_id
ORDER BY id DESC
LIMIT 1
UNION
-- record with current_id, if exists
SELECT *
FROM foo
WHERE id = :current_id
UNION
-- record with next id, if exists
SELECT *
FROM foo
WHERE id > :current_id
ORDER BY id ASC
LIMIT 1
) maybe_three_records
ORDER BY id;

Michael Glaesemann
grzm seespotcode net