Get previous and next records relative to an index record

[eeh]

Hi,

Suppose I have a table "product" with fields id,name,price. I want to
get a resultset of previous and current and next record when the
resultset is sorted by id and id=10. The table data is shown in the
following:

id Name Price
1 : :
5 : :<--------------previous record
10 : :<--------------id=10
12 : :<--------------next record
14 : :

How do I write the sql statement if I do not know 5 and 12 are the
previous and next id respectively?

Thanks!

[Bill Karwin]

eeh wrote:

id Name Price
1 : :
5 : :<--------------previous record
10 : :<--------------id=10
12 : :<--------------next record
14 : :

How do I write the sql statement if I do not know 5 and 12 are the
previous and next id respectively?

I would do this in application code. It's too difficult to construct a
single SQL query that returns these records and no other records.

Use a simple query for example:
SELECT id, Name, Price FROM myTable ORDER BY id;

Then fetch the records one by one in your application code until you get
to the record with id=10. Then fetch one more record. Pseudocode:

prev = curr = next = ();
WHILE true
DO
prev = curr;
curr = fetch_one_recor d();
IF (curr->{id} = 10) THEN BREAK;
DONE
next = fetch_one_recor d();

After the end, you should have the records you need stored in prev,
curr, and next.

Regards,
Bill K.

[Aggro]

eeh wrote:

id Name Price
1 : :
5 : :<--------------previous record
10 : :<--------------id=10
12 : :<--------------next record
14 : :

How do I write the sql statement if I do not know 5 and 12 are the
previous and next id respectively?

One simple and pretty fast (unless you need to run this inside a loop)
way is to do it with 3 queries:

select max(id) from product where id < 10; # returns 5
select min(id) from product where id > 10; # returns 12
select * from product where id in(5,10,12) order by id;

If the return value from min or max is NULL, then there is no previous
or next row in the database.

There is also the method Bill Karwin showed you, but this works a lot
faster and takes much less memory if you got thousands of rows in your
table. If there are only few rows in the table, then Karwin's method
will propably work faster.