CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11972

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

Upvotes: 9

Views: 81023

Answers (5)

hgfhfgh
hgfhfgh

Reputation: 1

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

Upvotes: 0

Roy T.
Roy T.

Reputation: 9638

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)

Upvotes: 10

Bharatkmr
Bharatkmr

Reputation: 131

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

Upvotes: 11

sqlRookie
sqlRookie

Reputation: 191

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.

Upvotes: 18

YC1207
YC1207

Reputation: 54

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

Try this.

Upvotes: 1

Related Questions