Dwain,

Thanks for the great idea, instead of the computed column, I used the bigint column with all the values.

working like a charm with index seek.

If the use the identity column, it will mess up the order since sql will order it differently.

I need data ordered by how sql orders it.

patrickmcginnis59 10839 (7/16/2013)

keebler96 (7/16/2013)

This is the problem. ...

If i upgrade to SQL Server 2012, Can I use the new analytical Functions?

The rownumber is very costly on a large table,

The Order By can be By Cost_Center_Code, Payment_Code, Inv_No

Thanks

Yes, Keebler96, this is exactly how i need the row.

I have updated the OP with better data.

The order of the previous and the next rows should be ordered just like...

All the three columns are primary keys. If I select one row by costcenter paymentcode and invno. I would like to know the previous and next costcenter psymentcode and invno.

The columns would be

prevcostcenter, prevpaymentcode, previnvno, nextcostcenter, nextpaymentcode, nextinvno.

all the columns define the next or previous row.

I have tried the rownumber the estimated rows in the execution plan...

I need all the columns in prev and next. The reason I want to avoid rownumbers because it scans the complete table and is slower if the data is...