sql - selecting rows either side of a record -

sql - selecting rows either side of a record -

i want select 4 rows either side of record images table have.

so far have following: -

with myresults as( select mykey, imagethumb,row_number() on (order mykey)as row images userid = @userid ) select * myresults row < ((select row myresults mykey=@imageid)+5) , row > ((select row myresults mykey=@imageid)-5)

this work okay wondering if there's better/simpler way of doing it

thanks

using self bring together u can accomplish target. seek this

with myresults (select row_number() on ( order mykey)as rn, mykey, imagethumb images userid = @userid) select * myresults bring together myresults b on a.rn between b.rn - 5 , b.rn + 5 a.mykey = @imageid

proof of answer

create table #images ( mykey int, imagethumb varchar(50) ); cte (select 1 id, newid() d union select id + 1, newid() d cte id < 10) insert #images select * cte; myresults (select row_number() on ( order mykey)as rn, mykey, imagethumb #images) select * myresults bring together myresults b on a.rn between b.rn - 3 , b.rn + 3 a.mykey = 4

sql sql-server tsql