Обсуждение: grabbing id of previous and next record for current select

Imagine you have a sequentially indexed table (just think any table with
an id field). 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. For the most part you would most likely assume this is simple
enough with the formula being:

id_current + 1 = id_next  (would yield the next record id)
id_current - 1 = id_previous (would yield you the previous record id)

HOWEVER:

The above formula does not work when there are records missing out of
the middle. For example... if you were to have 10 records, numbered 1
through 10 but there was no record 7 for example. If you were trying to
get the previous and next id's for the record 8, you could not rely on
the formula:

id_current - 1 = id_previous

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';

but there is no clear way I can think of at this time to get the
previous records id.


Basically I am writing an application that grabs a record from the
database and I want to at the same time grab the id's of the previous
record and the next record to send to my application to form the HTML etc.

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.

Any ideas? Anyone?

Would appreciate the help.

Alan

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