How to get previous row value

Question

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

Exequatur

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. – Act

possible duplicate of There is a way to access the "previous row" value in a SELECT statement? – Erlond

Possible duplicate of Is there a way to access the "previous row" value in a SELECT statement? – Transcendentalism

Answer 1

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)

Luing

Answer 2

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.

Casing

LAG() and LEAD() were added in SQL Server 2012 edition, the question was for 2005. learn.microsoft.com/en-us/sql/t-sql/functions/… – Gargan

Is there a way to fix the 0 in row 1? How does one get the previous value before the data set if it exists in the table? – Rapscallion

Answer 3

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

Grays

LAG() and LEAD() were added in SQL Server 2012 edition, the question was for 2005. learn.microsoft.com/en-us/sql/t-sql/functions/… – Gargan

Answer 4

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

Try this.

Eldredge

Answer 5

prev.row.sql() -1, SELECT * FROM DATABASE

Haystack

Welcome to StackOverflow. Please, edit and try for How to Answer, describe the effect of what you propose and explain why it helps to solve the problem. Find help with formatting your post here: stackoverflow.com/help/formatting . Consider taking the tour. – Mort

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Idolist