By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,474 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

Get previous and next records relative to an index record

P: n/a
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!

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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_record();
IF (curr->{id} = 10) THEN BREAK;
DONE
next = fetch_one_record();

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

Regards,
Bill K.
Jul 23 '05 #2

P: n/a
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.
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.