Above and below values of a id

Sai Reddy

7y885

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayesh Vaghasiya7y ago
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 and country = (select country from @sample where id=@id) order by ID desc) or
ID in (select top 2 ID from @sample where ID > @id and country = (select country from @sample where id=@id))or
ID = @id
Sai Reddy7y ago
Plese help to solve it, i need to apply it on lakhs of data. Thanks.
Salman Beg7y ago
;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 Shekar7y ago
select * from sample where id in(select top 2 id from sample where id <'9' and country =(select country from sample where id=9) ) or 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