SQLTeam.com | Weblogs | Forums

Find the record before and the record after

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.

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.