Best Way to Select Next/Previous Record 1

[BoulderBum]

I have sort of a random query that I can pretend exists in a vacuum. By "vacuum" I mean I won't really know the sort criteria or the keys to tell how to explicitly select the next/previous record. I will, however, have a distinct key for a record I want to select the previous/next record for, so I'm wondering how I can best fetch the previous/next record for a random query.

e.g. If I have a table with the rows:

record 12
record 99
record 19
record 17

and I know I have a record cooresponding to 19, what would be the best way to fetch the next record (cooresponding to 17).

I was thinking of maybe declaring a cursor, then doing a fetch previous/next, but I'm not sure how to quickly navigate to e.g. record 19 in the example above, and I'm wondering if there's a better way to do it anyway.

 

[amrita418]

BoulderBum,
One way of doing this would be to create a temp table which has an identity column that identifies each row uniquely. Once you populate data into this table you can do a self join to get the necessary columns.

Regards,
--aa

 

[EdwinGene]

You don't say what the value is in your "distinct record key". For the purposes of this reply I am just going to assume that the "distinct record key" is unique, numeric, non-sequential (meaning there are gaps), and not in order.

Declare @RecordKey Int

-- Get the first "distinct record key"
Select
@RecordKey = Min(RecordKey)
From
dbo.TableA with (NoLock)

While 1 = 1
Begin
-- Do processing with @RecordKey
-- @RecordKey processing statements

-- Check if last RecordKey has been processed.
-- This check allows for new RecordKeys being inserted
-- while the processing is occurring.
-- If that isn't going to happen, select @MaxRecordKey
-- before the While loop, and just use that value for comparison.
If @RecordKey =
(
Select
Max(RecordKey)
From
dbo.TableA with (NoLock)
)
Begin
Break
End

-- Get the next "distinct record key"
Set @RecordKey =
(
Select
Min(RecordKey)
From
dbo.TableA with (NoLock)
Where
RecordKey > @RecordKey
)

End

 

[BoulderBum]

Thanks, guys!

Unfortunately the records aren't in sequence by record key, though, so I can't make "RecordKey > @RecordKey" comparisons or it will jump over a bunch of records.

amrita418,

I ended up adopting that strategy. Cheers!

 

[EdwinGene]

That's what the 'Where' clause is for. It finds the smallest record key which is larger than the current record key.

 

[EdwinGene]

And when I say RecordKey, I am talking about your 'record 12', 'record 17' column.

 

[BoulderBum]

Thanks again, Edwin.

Perhaps I'm misunderstanding how the above works, but if I had keys in this sequence:

5
17
88
9
21

and I was on "5", wouldn't the above just jump to "9" whne getting next, then loop until it ended up at "88"?

 

[EdwinGene]

You didn't want to do that. I misread your original post. I thought you wanted to access the columns in record key order.

If all you want to do is get the next record, regardless of the value in the record key column, the identity column would be the best solution.

 

[BoulderBum]

Oh. Sorry about the confusion there.

I do appreciate your help, man. Thanks again.