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

Question

If we have a table called FollowUp and has the lines [ID (int), Value (Money)] and we have several 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 will receive each row identifier, the value and the previous row value, which displays the following data
ID --- Value --- Prev_Value
1 ----- 70 ---------- 0
2 ----- 100 -------- 70
3 ----- 150 -------- 100
8 ----- 200 -------- 150
20 ---- 250 -------- 200
45 ---- 280 -------- 250

I am making the following query, but I think it is so poor in performance with a 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 get a better solution for such a problem?

Answer 1

This sql should work better than the one you have above, although these types of queries tend to have little performance ... so anything you can put into them to limit the size of the dataset you are looking at will help tremendously. For example, if you are viewing a specific date range, enter this.

 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 

NTN

Answer 2

You can use the OVER operator to create beautifully growing line numbers.

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

Using numbers, you can easily find the previous line:

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

Answer 3

This is not the answer to your real question.

Instead, I feel that you are approaching the problem from the wrong direction: In properly normalized relational databases, the tuples ("rows") of each table should contain references to other db elements instead of the actual values. Maintaining this relationship between tuples refers to the part of entering code base data. That is, if you contain the value of the tuple with the closest, the smaller identifier number really belongs to your data model.

If the requirement to find out the previous value comes from a part of the application’s view - that is, one view into the data that needs to be formatted in a certain order - you have to pull out the contents, sort by id and process the request in view of the specific code.

In your case, I would suggest that knowing the meaning of previous tuples would really belong to the view code instead of the database.

EDIT: You mentioned that you store them separately and just want to make a request for it. Even so, the application code is likely to be a more logical place for this association.

Answer 4

How about pulling strings into your application and compute the previous value there?

Answer 5

Create a stored procedure and use the cursor to iterate and create strings.

Answer 6

You can use the "LAG" function.

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