sql server - query to find the previous and next rows from the current one which includes order by clause -

i execute following query:

select quotesid  quotestable  quotesauthor '%mahatma%'   order popularity desc 

which gives me following results: (quotesid - primary-key)

 968  158  159  160  161 

my requirement:

i need next , previous quotesid (order popularity desc). example if in 158th record. next , previous values should 159 , 968 respectively need in single query.

query

select *   quotestable       (quotesid = (select min(quotesid) quotestable quotesid > 158 , quotesauthor '%mah%' )   or quotesid = (select max(quotesid) quotestable quotesid < 158 , quotesauthor '%mah%' )) order popularity desc 

this not work. gives me 1 record - 159. there way write single query , results desired?

fiddle:

sql-fiddle

perhaps, work you. gives query row number index , left outer join shifted +1 , -1 record produce [next] , [previous] columns.

with cte (     select quotesid, row_number() on (order popularity desc) [rowno]     quotestable ) select cte.quotesid, [next].quotesid [next], [prev].quotesid [previous] cte left outer join cte [next] on cte.rowno = ([next].rowno - 1)          left outer join cte [prev] on cte.rowno = ([prev].rowno + 1) 

result:

quotesid next   previous ------------------------ 968      158    null 158      159    968 159      160    158 160      161    159 161      null   160 

sql fiddle: http://sqlfiddle.com/#!6/0ac0b/4