Find the record before and the record after

1

I want to identify what the records are of a field before and after the field. So if I set the row number and order by to the field how can I retrieve the record before that and the record after that?

I have a field set for vehicle vin. I want to see if the closest examples of that vin. In order to do this I'm reversing the field.

select row_number () over (order by reverse(vin)) as row#, reverse(vin), datecreate
from VHVIN

I have a report that shows all new vins created. I want to pick the record before and after the field and show them in the report. There are about 20 fields daily that needs to be reported on.

2

If you are using SQL 2012 or higher, you can use the LAG and LEAD functions to access the previous and next records. If you're using an earlier version, you' d need to self-join twice, based on prev.row# = my.row#-1 and next.Row# = my.Row# + 1.