How to calculate the value difference in each row

Question

I have to calculate the weight difference of the given dataset based on the previous row of each group ids.

For instance:

Expected Result:

ID  Wt    diff
1   60.5  2.5
1   58    -4
1   62     
2   89    14
2   75    3
2   72

Kindly suggest code to solve the problem. Thanks in advance

It is easy but you first need to explain what determines if one row is previous to the other. What determines that 58 comes before 52 but after 60.5 — Salman A, Sep 09 '21 at 09:27
Does this answer your question? [Is there a way to access the "previous row" value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — Thom A, Sep 09 '21 at 09:28
Actually, I sorted the dataset by ID and Date column then I calculate the code you suggest. — Jammy, Sep 09 '21 at 09:29

score 0 · Accepted Answer · answered Sep 09 '21 at 09:39

there are analytics functions for this in SQL the names are Lag for previous row and lead for nest row

for your example

select ID,Wd, Wd - lead(Wd) over(partition by ID order by {insert ordar by column name{date}) as diff from table_name

1 Answers1