How to calculate diff between two non consecutive rows in sql-CodePudding

I am trying to calculate the third column, as a result of diff between first row and second row, first row and third row and so on using sql. would anybody help me ?

CodePudding user response：

Assuming your table is sorted by col1 with alphabetical order,

SELECT c1, c2, c2 - FIRST_VALUE(c2) OVER (ORDER BY c1) AS c3
 FROM `your_table`;

will output below result.

CodePudding user response：

In the example you have provided where we have a unique value a that corresponds to the value 10 under column2, I believe we could do something like this for a solution:

SELECT
    a.column1,
    a.column2,
    (a.column2 - b.column2) AS column3
FROM table a
OUTER APPLY (
    SELECT column2
    FROM table
    WHERE column1 = 'a'
) b

This will get you a way to hold the value for 10 statically inside of the outer apply, so it can be subtracted from each value that is passed in.