How to get previous row value

How to get a value from previous result row of a SELECT statement

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

ID --Value

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

so on...

How t开发者_开发知识库o make one Query that get each row ID,Value and the previous Row Value in which data appear as follow

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

so on.

So can anyone help me to get the best solution for such a problem ?

Need Query Help

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

This should work. The Lag function gets the previous row value for a specific column. I think this is what you want here.

You can use LAG() and LEAD() Function to get previous and Next values.

SELECT 
   LAG(t.Value) OVER (ORDER BY t.ID) PreviousValue,
   t.value Value,
   LEAD(t.value) OVER (ORDER BY t.ID) NextValue
FROM table t

GO

You would have to join the table with itself, I'm not sure if this is 100% legitimate SQL, but I have no SQL-Server to try this at the moment, but try this:

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

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

Try this.