CODEWITHSUNDEEP
sqlms-access
Amr Badawy
Amr Badawy

Reputation: 7673

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

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 ?

Upvotes: 4

Views: 19606

Answers (6)

Jenna
Jenna

Reputation: 75

You could use the function 'LAG'.

     SELECT ID,
            Value,
            LAG(value) OVER(ORDER BY ID) AS Prev_Value
     FROM FOLLOWUP;

Upvotes: -1

Praesagus
Praesagus

Reputation: 2094

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

Upvotes: 5

Jukka Dahlbom
Jukka Dahlbom

Reputation: 1740

This is not an answer to your actual question.

Instead, I feel that you are approaching the problem from a wrong direction: In properly normalized relational databases the tuples ("rows") of each table should contain references to other db items instead of the actual values. Maintaining these relations between tuples belongs to the data insertion part of the codebase. That is, if containing the value of a tuple with closest, smaller id number really belongs into your data model.

If the requirement to know the previous value comes from the view part of the application - that is, a single view into the data that needs to format it in certain way - you should pull the contents out, sorted by id, and handle the requirement in view specific code.

In your case, I would assume that knowing the previous tuples' value really would belong in the view code instead of the database.

EDIT: You did mention that you store them separately and just want to make a query for it. Even still, application code would probably be the more logical place to do this combining.

Upvotes: 2

Andomar
Andomar

Reputation: 238076

You can use the OVER statement to generate nicely increasing row numbers.

select
    rownr = row_number() over (order by id)
,   value
from your_table

With the numbers, you can easily look up the previous row:

with numbered_rows
as (
    select
        rownr = row_number() over (order by id)
    ,   value
    from your_table
)
select
    cur.value
,   IsNull(prev.value,0)
from numbered_rows cur
left join numbered_rows prev on cur.rownr = prev.rownr + 1

Hope this is useful.

Upvotes: 3

Jason Cohen
Jason Cohen

Reputation: 83011

Create a stored procedure and use a cursor to iterate and produce rows.

Upvotes: -1

Manuel
Manuel

Reputation: 6442

What about pulling the lines into your application and computing the previous value there?

Upvotes: -1

Related Questions