Re: Simple SQL question

From: Martin Erdely <merdely_at_astound.dialix.oz.au>
Date: 1995/06/03
Message-ID: <50_at_astound.dialix.oz.au>#1/1

 

In article <D9JLv8.Iz_at_inter.NL.net>, D. Hoogvorst (dhoogvor_at_inter.NL.net) 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
>
David
Using PLSQL V2, put your select statement in a cursor (leave out the rownum predicate) and fetch the first record. set serveroutput on
DECLARE
   prev_name varchar2(30);
   cursor c_prev_name is

      select name 
      from names
      where name < 'Peter'
      order by name desc;

BEGIN
  OPEN c_prev_name;
  FETCH c_prev_name into prev_name;
  dbms_output.put_line('The name before Peter is '||prev_name);   CLOSE c_prev_name;
END; Hope this helps.

---

Martin Erdely Internet: merdely_at_astound.dialix.oz.au

"Bug free Software. I'd like to see that" Received on Sat Jun 03 1995 - 00:00:00 CEST