sql server - Display records in a table with NextID and PrevID -
i have of following fields in quotestable : quotesid primarykey
┌──────────┬────────────┬────────────────────────────────────────────┐ │ quotesid │ quotesdesc │ quotestags │ ├──────────┼────────────┼────────────────────────────────────────────┤ │ 75 │ quotes 1 │ leadership, integrity, values │ │ 100 │ quotes 2 │ leadership, faith │ │ 102 │ quotes 3 │ heartpower, motivation, leadership │ │ 105 │ quotes 4 │ mercy, power, military, leadership │ │ 209 │ quotes 5 │ compassion, confidence, leadership, family │ └──────────┴────────────┴────────────────────────────────────────────┘
i have query select * quotestable contains (quotestags, 'leadership')
my requirement in webpage if display quotes having quoteid = 102, want next 3 quotes displayed in iframe. ie., 105, 209, 75
if webpage displays quotes = 209 want next 3 quotes 75,100,102 (if ends start beginning)
also need 2 columns have nextquoteid , prevquoteid in resultant query point next , previous quotesid. donot know how put these 2 columns in final query.
i using sql-server 2008 r2 version.
answer sql server 2012+
we did not know sql server 2008 until 2 hours after answer. done sub-queries if bothered. i'm not because got downvote not knowing version answer required for
select top 4 quotesid = sortquotesid % 1000000, quotesdesc, quotestags, nextquoteid = (lead(sortquotesid) on (order sortquotesid))% 1000000, prevquoteid = (leag(sortquotesid) on (order sortquotesid))% 1000000 ( select sortquotesid = quotesid, quotesdesc, quotestags quotestable contains (quotestags, 'leadership') union select sortquotesid = quotesid+1000000, quotesdesc, quotestags quotestable ) x order sortquotesid
Comments
Post a Comment