SQL - Get the value from the next lowest ID

Question

I'm working in SQL Server 2014 Management Studio.

Not really sure how to explain this but it's best if I just explain with an example.

So I've figured out how to get the next lowest ID, that is fairly simple. But once i get that row i need to take the value from it and apply it to the next highest value.

If I have 4 rows

ID      value
-------------
10        50
30       200
20        75
25       100

I want to take the value each row and applying to the row with the next highest ID. So it should look like this.

ID      value
-------------
10      null or 0
30      100
20       50
25       75

Since there is no row before 10 ID, that row should have a value of null or 0, doesn't matter. And the others should just follow the pattern of taking the value from the row with the next lowest ID.

Answer 1

You're looking for LAG():

Select  Id, Lag(Value) Over (Order By Id) As Value
From    YourTable;

Working demo:

Declare @YourTable Table
(
    Id      Int,
    Value   Int
);

Insert @YourTable
Values (10, 50), (30, 200), (20, 75), (25, 100);

Select Id, Lag(Value) Over (Order By Id) As Value
From   @YourTable;

Results

Id  Value
10  NULL
20  50
25  75
30  100