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

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, Feb 01 '11 at 07:40
possible duplicate of [There is a way to access the "previous row" value in a SELECT statement?](http://stackoverflow.com/questions/710212/there-is-a-way-to-access-the-previous-row-value-in-a-select-statement) — Linus Kleen, Feb 01 '11 at 07:40
Possible duplicate of [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — MatSnow, Nov 15 '17 at 13:58

score 18 · Answer 1 · edited Jun 16 '20 at 12:13

18

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.

edited Jun 16 '20 at 12:13

HK boy

1,398
11
17
25

answered Jul 26 '17 at 03:46

sqlRookie

191
1
4

LAG() and LEAD() were added in SQL Server 2012 edition, the question was for 2005. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15 – Darren Griffith Dec 27 '21 at 20:40
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? – Jwan622 May 10 '22 at 19:09

score 10 · Accepted Answer · edited May 24 '22 at 06:43

10

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)

edited May 24 '22 at 06:43

Damini Suthar

1,470
2
14
43

answered Feb 01 '11 at 07:42

Roy T.

9,429
2
48
70

score 9 · Answer 3 · edited Sep 14 '20 at 18:52

9

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

edited Sep 14 '20 at 18:52

Tejasvi Hegde

2,694
28
20

answered Apr 25 '16 at 12:31

Bharatkmr

111
1
4

LAG() and LEAD() were added in SQL Server 2012 edition, the question was for 2005. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15 – Darren Griffith Dec 27 '21 at 20:40

score 1 · Answer 4 · edited Apr 06 '17 at 04:39

1

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

Try this.

edited Apr 06 '17 at 04:39

Pang

9,564
146
81
122

answered Apr 06 '17 at 04:22

YC1207

54
3

How to get previous row value

4 Answers4

Linked