Re: Simple SQL question

>I don't know much about SQL, and maybe this is a stupid question,
>but my problem is the following:
>
>How in SQL can I retrieve a record (just one) directly prior or next
>to a given record in accordance with the primary key.
>
>So, if in the database I have the following names:
>
>...
>...
>John
>Peter
>Richard
>...
>...
>
>and I know that Peter is an existing record, how do I get the
>names just before and just after Peter.
>
>To retrieve the next record, I issue the following query:
>SELECT NAME from NAMES where NAME > 'Peter' and rownum < 2
>
>If I try to retrieve the previous name using:
>SELECT NAME from NAMES where NAME < 'Peter' and rownum < 2
>I get the very first name in the database, even if I add
>an ORDER BY NAME DESC. (No surprises here).
>
>I definitely don't want to run through the whole database to get
>the previous name, and I'm sure there should be an easy way to do it.
>
>Any help is greatly appreciated.
>David. e-mail : dhoogvor_at_huminf.nl
>

Hi, David !

If your table is indexed on name and you use Oracle 7.x it is possible to use hints:

To retrieve next record:

SELECT /*+INDEX_ASC your_table name_index*/ name FROM your_table   WHERE name>'Peter' and rownum=1

To retrieve previous record:

SELECT /*+INDEX_DESC your_table name_index*/ name FROM your_table   WHERE name<'Peter' and rownum=1

See Application Developer's Guide, Chapter 5 for details.

Note that your select for next record is erroneous - it may return any record with name>'Peter' rather than the closest one.

Alexandr Alesinsky
JSV Investor Received on Wed Jun 07 1995 - 00:00:00 CEST