How to get the value of the previous row

Question

How to get value from previous line of SELECT statement result

If we have a table called cardevent and has the rows [ID (int), Value (Money)], and we have several rows, for example

ID --Value

1------70 
1------90
2------100
2------150
2------300 
3------150 
3------200 
3-----250 
3-----280

etc...

How to make one request that receives each row identifier, the value and the previous value of the row in which the data is displayed, as follows

ID --- Value ---Prev_Value

1 ----- 70 ----------  0 
1 ----- 90 ---------- 70
2 ----- 100 --------  90 
2 ------150 -------- 100
2 ------300 -------- 150
3 ----- 150 -------- 300 
3 ----- 200 -------- 150 
3 ---- 250 -------- 200 
3 ---- 280 -------- 250

etc.

So can anyone help me get a better solution for such a problem?

Query Request Help

Answer 1

You will need to join the table with yourself, I'm not sure if this is 100% legal SQL, but I do not have SQL-Server to try this at the moment, but try the following:

select (ID, Value) from table as table1 join
inner join table as table2
on table1.ID = (table2.ID -1)

Answer 2

SELECT t.*, LAG(t.Value) OVER (ORDER BY t.ID) FROM table AS t

. Lag . , , .

Answer 3

Check out the previous answer to this problem.

Is there a way to access the "previous line" value in a SELECT statement?

Answer 4

You can use the LAG () and LEAD () functions to get the previous and next values.

SELECT t.Value OVER (ORDER BY t.ID) PREVIOUS_VALUE,
t.value VALUE,
LEAD(t.value) OVER (ORDER BY t.ID) NEXT_VALUE
FROM TABLE T

GO

Answer 5

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

Try it.