Sql – How to get a value from previous result row of a SELECT statement

ms-accesssql

If we have a table called FollowUp and has rows [ ID(int) , Value(Money) ]
and we have some rows in it, for example
ID –Value
1——70
2——100
3——150
8——200
20—–250
45—–280
and we want to make one SQL 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
2 —– 100 ——– 70
3 —– 150 ——– 100
8 —– 200 ——– 150
20 —- 250 ——– 200
45 —- 280 ——– 250

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

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

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

Best Answer

This sql should perform better then the one you have above, although these type of queries tend to be a little performance intensive... so anything you can put in them to limit the size of the dataset you are looking at will help tremendously. For example if you are looking at a specific date range, put that in. 

SELECT followup.value, 
    ( SELECT TOP 1 f1.VALUE 
      FROM followup as f1 
      WHERE f1.id<followup.id 
      ORDER BY f1.id DESC
    ) AS Prev_Value
FROM followup

HTH

Related Topic