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