Re: Simple SQL question

From: John Howard <jphoward_at_amoco.com>
Date: 1995/06/02
Message-ID: <3qn3ih$ab_at_tabloid.amoco.com>#1/1


In article <D9JLv8.Iz_at_inter.NL.net>, dhoogvor_at_inter.NL.net (D. Hoogvorst) writes:
>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
>
>
How about :-

SELECT MAX(NAME) FROM NAMES WHERE NAME < 'Peter';

and
SELECT MIN(NAME) FROM NAMES WHERE NAME > 'Peter';

-- 
John Howard				Data Management
Snr Programmer/Analyst			Technical Services
(Socon 822 7084)			Amoco (UK) Exploration Co.
Received on Fri Jun 02 1995 - 00:00:00 CEST

Original text of this message