>>>>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.
I'm often telling clients that I believe there is a lack of "historically accurate" thinking in IT. This manifests in a few ways, one of which is "physical record". Prior record was entered before current record. This suggests a need for a datetime stamp.
Then it is a simple matter to get the max(datetime) before current record and the min(datetime) after the current record and to use those datetime stamps to get the records.