Loading

Finding previous and Next value

Finding previous and Next value

Sai Reddy

Sai Reddy

8y801

Hi,

Please help me to acheive this requirement.

Input:

id	Value
1	100
2	200
3	300
4	400
5	500

Output:

id	Value	Pre	Nxt
1	100	0	200
2	200	100	300
3	300	200	400
4	400	300	500
5	500	400	0

2 Replies

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

Jenith Thakkar8y ago
Accepted answer
1. SELECT
2. Id,
3. p.VAlue
4. LAG(p.Value) OVER (ORDER BY p.Id) PreviousValue,
5. LEAD(p.Value) OVER (ORDER BY p.Id) NextValue
6. FROM table p
7. GO

Packiaraj Santhiyagu

Packiaraj Santhiyagu8y ago

Hi Sai,

Try this it will work.

create table previNext(id int, [value] int)
insert into previNext(id,[value])
select 1,100
insert into previNext(id,[value])
select 2,200
insert into previNext(id,[value])
select 3,300
insert into previNext(id,[value])
select 4,400
insert into previNext(id,[value])
select 5,500
select * from previNext
select id, ISNULL(LAG([value])OVER (ORDER BY id ),0) previousVlaue,[value],
ISNULL(LEAD([value])OVER (ORDER BY id ),0) nextVlaue from previNext

Output:

1 0 100 200
2 100 200 300
3 200 300 400
4 300 400 500
5 400 500 0