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

Search Discussions

  • Michael Glaesemann at Jan 11, 2007 at 10:10 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 11, '07 at 9:18p
activeJan 11, '07 at 10:10p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase