Re: Simple SQL question

dhoogvor_at_inter.NL.net (D. Hoogvorst) 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 > .... > ....
> and I know that Peter is an existing record, how do I get the
> names just before and just after Peter...

If NAME is the primary key of your NAMES table, then this should work for you:

1. To get the name immediately before Peter: SELECT MAX(NAME) FROM NAMES WHERE NAME < 'Peter'
2. To get the name immediately after Peter: SELECT MIN(NAME) FROM NAMES WHERE NAME > 'Peter'

If you do this particular query often, and if the NAMES table is large, then you should have a unique index of NAME on the NAMES table. This will give the best performance, since the query can be answered just by reading the index. Received on Sat Jun 03 1995 - 00:00:00 CEST