HOW TO SELECT 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.

i make the following query but it's so bad in performance in huge amount of data

SELECT cardevent.ID, cardevent.Value, 
      (SELECT F1.Value
       FROM cardevent as F1 
       where F1.ID = (SELECT  Max(F2.ID)
                      FROM cardevent as F2 
                      WHERE F2.ID < cardevent.ID)
      ) AS Prev_Value
FROM cardevent

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

Answer 1

Your query looks OK, but I suspect the performance problem is because it's an O(n²) algorithm. An outer select for the rows, and an inner select that looks for the biggest row less than the current one. Even with indexing, this can take some time to get the max values.

You could try the following query instead, as it's possible you can gain performance from indexing. I'm not sure your query is being optimized using a join (take a look at the plan for your query and make sure it's using the proper indexes). This is still technically O(n²), but indexing could cut this significantly:

SELECT e.ID, e.Value, prev.value 
FROM cardevent AS e
LEFT JOIN cardevent AS prev ON prevevent.ID = (SELECT MAX(e2.ID)
                                               FROM cardevent AS e2
                                               WHERE e2.ID < e.ID);

Another way might be to use a stored procedure that makes one pass through and would be O(n) instead. Pseudocode:

initialize prev_value = 0

foreach row in cardevent:
    Return the row //row.ID, row.value, @prev_value
    Set prev_value = row.value //for the next time

You'll have to see which one ends up being faster.

---

Also, if you only need these values in a client application and are returning every row all the time, you can push this calculation to the client instead. One additional pass through the returned rows will give the previous value:

for i=0 to returnedRows.length - 1:
    Set prev_value = (i == 0 ? 0 : returnedRows[i-1].value)

Answer 2

Try something like this:

SELECT C.ID, C.Value, COALESCE(C1.Value, 0)
FROM cardevent C
    LEFT JOIN cardevent C1
    ON C1.Id = (SELECT MAX(id) FROM cardevent C2 where C2.Id < C.Id)

Answer 3

This one should work fine for both databases:

SELECT cardevent.ID, cardevent.Value, 
  (SELECT TOP 1 F1.Value
   FROM cardevent as F1
   WHERE F1.ID < cardevent.ID
   ORDER BY F1.ID DESC
   ) AS Prev_Value
FROM cardevent

Update: Assuming that ID is not unique, but that the combination of ID and Value is unique (you must specify what imposes your ordering on the table, so one knows what is the previous row), you must use this query:

select cardevent.ID, cardevent.Value, 
    (select TOP 1 F1.Value from cardevent as F1
     where (F1.ID < cardevent.ID) or (F1.ID = cardevent.ID and F1.Value < cardevent.Value)
     order by F1.ID DESC, F1.Value DESC
    ) AS Prev_Value
from cardevent

Answer 4

How does following perform?

SELECT F1.ID, F1.Value, F3.Value
FROM cardevent F1
     INNER JOIN (
        SELECT F1.ID, mID = MAX(F2.ID)
        FROM cardevent F1
             INNER JOIN cardevent F2 ON F2.ID < F1.ID
        GROUP BY F1.ID
      ) F2 ON F2.id = F1.ID
     INNER JOIN cardevent F3 ON F3.ID = F2.mID
UNION ALL
SELECT F1.ID, F1.Value, 0
FROM cardevent F1
     INNER JOIN (
        SELECT ID = MIN(ID) 
        FROM cardevent
      ) F2 ON F2.ID = F1.ID
ORDER BY 1

Answer 5

I prefer TOP 1 instead of MAX(...), which IMHO should be also faster:

SELECT      C.ID, C.Value, COALESCE(C1.Value, 0)
FROM        cardevent C
LEFT JOIN   cardevent C1
        ON  C1.ID = (SELECT TOP 1 C2.ID FROM cardevent C2 WHERE C2.ID < C.ID ORDER BY C2.ID)