>>>Would this work?
>>>
>>>select * from myTable where key = myKey ;
>>>union ;
>>>select top 1 * from myTable where name < (select Name from myTable where key=myKey) order by name descending;
>>>select top 1 * from myTable where name > (select Name from myTable where key=myKey) order by name ascending
>>>
>>>
>>
>>However, it assumes that the base table was sorted on name. If it was not sorted at all, then we need to use recno() analogue.
>
>Maybe I'm missing something, but any existing sort or index order on the base table should not matter, we're creating a new cursor. You'd need to set an overall ORDER for the final result set. But I think your sample code should work where:
>
>- you want to find the "current" row by some PKey value
>- you want to find Previous and Next by some other column (such as Name, in your example)
>
>As long as there are appropriate index tags on the columns referenced the query should even be at least partially Rushmore optimizable.
I read Don's request as he wants the physical prior and next record from the table in addition to the key record.
If it's not broken, fix it until it is.
My Blog