Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
iulianionescu
Starting Member
14 Posts |
 Posted - 2010-01-13 : 15:45:11
|
| Hi, I am trying to give the user the ability to navigate to the next or previous records while looking at a certain record. The issue is that the list of records is sorted by last change date and not by record ID. So, my data might look like this:ID Date7 01/01/20092 01/02/20098 01/04/20091 01/10/20095 01/11/2009If I am currently watching record ID 8 I would like to generate links for previous and next to show records 2 and 1, respectively.I was thinking of a way to do that without a cursor but I just can't figure it out... If anyone can give me any suggestion or at least a starting point, I'd appreciate it. Basically I need a way to retrieve the IDs 2 and 1 as the previous and next record ids...Thank you!IulianRegards,Iulian |
|
|
queenofcode
Starting Member
6 Posts |
Posted - 2010-01-13 : 15:54:40
|
| If the sort order is always the date, you could use these:Next record:SELECT id from table where date in (select MIN(date) from table where date > current_date)Previous record:SELECT id from table where date in (select MAX(date) from table where date < current_date)<geek> The Self-Proclaimed Queen of Code :) </geek> |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 03:21:56
|
| Nice and elegant & efficient, as ever Peso. I reckon SELECT TOP 1 will be more efficient than MAX < CurrentDate (particularly if there is an index on the Date column)"See reponse made by me 01/09/2010 : 18:15:18"Like this, eh?!!:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138108#538485 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-14 : 04:09:37
|
Oh yes... I always forget the syntax to point directly to an answer.I guess I wish there was a button on every answers to click, so that the direct link to the answer was put on the clipboard.The suggestion in the link above also has the advantage that it "cycles". When "last" record is reached, "first" record reused, and vice versa.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 04:18:28
|
| "I guess I wish there was a button on every answers to click, so that the direct link to the answer was put on the clipboard."Snitz is 10 years old! You'll be wanting AJAX postings next ... Sheesh!!My preference is for next/previous not to "cycle" at the ends, but that's an easy modification of course. |
|
|
|
iulianionescu
Starting Member
14 Posts |
Posted - 2010-01-14 : 16:30:31
|
| Thanks, everyone! Using all the suggestions I was able to do what I was looking for!Thank you!IulianRegards,Iulian |
|
|
|
|
|
|
|
|
Previous / Next records