How to get previous row value

8
votes

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 to 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

4
sqlsql-serversql-server-2005
Your two rows [1, 70] and [1, 90] could be stored in either order in the database. How would pick which one is before the other? It might change from moment to moment based on internal database data-structure re-balancing. - sarnold
possible duplicate of There is a way to access the "previous row" value in a SELECT statement? - Linus Kleen
Possible duplicate of Is there a way to access the "previous row" value in a SELECT statement? - MatSnow

4 Answers

14
votes
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.

7
votes

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 join
inner join table as table2
on table1.ID = (table2.ID -1)
6
votes

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
1
votes
select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

Try this.