Re: Simple SQL question

dhoogvor_at_inter.NL.net (D. Hoogvorst) wrote:
>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.
>

Hi David,
General solution is something like this
select * from
table x
where primary_key =
(select max(primary_key)
 from table y
 where y.primary_key < the_value_you_want_to_search_for) So in this case
select name from names x
where name =
(select max(name) from names y
 where y.name < 'John')

To get the next value replace max by min and < by > in the subquery

Sybrand Bakker
Senior I.S. Analyst
Bentley Systems Europe Received on Thu Jun 08 1995 - 00:00:00 CEST