Re: Simple SQL question

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

: To retrieve the next record, I issue the following query:
: SELECT NAME from NAMES where NAME > 'Peter' and rownum < 2

Careful here! This may seem work now, but that is purely by accident. There is no guarantee that Oracle will keep your records in the order you inserted them. This query could return ANY record where NAME > 'Peter'.

: 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).

This doesn't work because rownum is assigned to output rows before any sorting is done.

Although these points don't solve your problem, I see that others have handled that capably. I hope these couple of tips help avoid future problems!

Alan Holsztynski
alanh_at_mail.msen.com Received on Thu Jun 08 1995 - 00:00:00 CEST