Above and below values of a id

Question

Hi ,Please help me to solve it. create table sample ( ID int IDENTITY, country varchar(10), UNI int, ) insert into sample values('INDIA',1000),('INDIA',1001),('INDIA',1002),('INDIA',1003),('INDIA',1004),('INDIA',1005), ('AUS',1001),('AUS',1002),('AUS',1003),('AUS',1004),('AUS',1005),('US',1001),('US',1002),('US',1003),('US',1004); select * from sample; When i pass the particular id from one group of countr i need the 2 above and 2 below of passed number from the same country. Ex: if i pass the id=9 then result should be as follows 7 AUS 1001 8 AUS 1002 9 AUS 1003 10 AUS 1004 11 AUS 1005

Jayesh Vaghasiya · Answer

Try This 100 % declare @sample table (ID int IDENTITY, country varchar(10), UNI int) declare @id int = 9 insert into @sample values('INDIA',1000),('INDIA',1001),('INDIA',1002),('INDIA',1003),('INDIA',1004),('INDIA',1005), ('AUS',1001),('AUS',1002),('AUS',1003),('AUS',1004),('AUS',1005),('US',1001),('US',1002),('US',1003),('US',1004); select * from @sample where ID in (select top 2 ID from @sample where ID ID in (select top 2 ID from @sample where ID > @id and country = (select country from @sample where id=@id))or ID = @id

Sai Reddy · Answer

Plese help to solve it, i need to apply it on lakhs of data. Thanks.

Salman Beg · Answer

;With cte as (select * from sample where Id=9) Select t4.country as secondprevious, t2.country as firstprevious, t1.country, t3.country as firstnext, t5.countrt as secondnext From sample as t4, sample as t2, cte as t1, sample as t3, sample as t5 where (T2.id+1)= t1.id and (t3.id+1)=t1.id and (t4.id+2)=t1.id and (t5.id-2)=t1.id

Madan Shekar · Answer

select * from sample where id in(select top 2 id from sample where id '9' and country =(select country from sample where id=9)) if it helps mark as ans any query ping me