How can I subtract a previous row in sql?

Question

What should I query if I wanted to subtract the current row to the previous row. I will use it on looping in vb6. Something Like this:

Row
1
2
3
4
5

On first loop value 1 will not be deducted because it has no previous row, which is ok. Next loop value 2 will then be deducted by the previous row which is value 1. And so on until the last row.

How can I achieve this routine? By SQL query or VB6 code.Any will do.

Keep in mind there's no such thing as "previous row" in a SQL result set unless you use an "ORDER BY" clause to define the order. Also, you should try to think in terms of sets with relational databases, so I hope someone gives you a set-wise answer (I have to leave now). — John Saunders, Jul 10 '13 at 01:08
.,yes.but how can I define that in order by clause?thanks... — ImTheBoss, Jul 10 '13 at 01:13
Outline (sorry I have to run): define a CTE that uses ORDER BY to choose an order, and then uses [ROW_NUMBER](http://technet.microsoft.com/en-us/library/ms186734.aspx) to add a column defining the order. Then join the CTE to itself on x.ROWNUM = y.ROWNUM+1, and include x.value-y.value. — John Saunders, Jul 10 '13 at 01:16

Gordon Linoff · Accepted Answer · 2018-01-06T06:53:32.357

35

Assuming you have an ordering column -- say id -- then you can do the following in SQL Server 2012:

select col,
       col - coalesce(lag(col) over (order by id), 0) as diff
from t;

In earlier versions of SQL Server, you can do almost the same thing using a correlated subquery:

select col,
       col - isnull((select top 1 col
                     from t t2
                     where t2.id < t.id
                     order by id desc
                    ), 0)
from t

This uses isnull() instead of coalesce() because of a "bug" in SQL Server that evaluates the first argument twice when using coalesce().

You can also do this with row_number():

with cte as (
      select col, row_number() over (order by id) as seqnum
      from t
     )
select t.col, t.col - coalesce(tprev.col, 0) as diff
from cte t left outer join
     cte tprev
     on t.seqnum = tprev.seqnum + 1;

All of these assume that you have some column for specifying the ordering. It might be an id, or a creation date or something else. SQL tables are inherently unordered, so there is no such thing as a "previous row" without a column specifying the ordering.

edited Jan 06 '18 at 06:53

answered Jul 10 '13 at 01:16

Gordon Linoff

1,242,037
58
646
786

1

What if he doesn't have an ordering column? ROW_NUMBER? – John Saunders Jul 10 '13 at 01:17
Is there a particular reason to use COALESCE on the LAG, rather than the 'default' argument to the LAG function? – v010dya Jun 28 '14 at 06:46
@Volodya . . . Habit. `lag()` and `lead()` take several arguments. I can remember the first two easily -- the column and the offset. – Gordon Linoff Jun 28 '14 at 13:25
There's a table `cte` created, but the reference beneath it is to table `t`. Fix? – Pierre L Jan 05 '18 at 22:15
@fortune_p . . . Oops. Thank you for the feedback. – Gordon Linoff Jan 06 '18 at 06:53
Lot of thx! It helped me! – Stanley Gong Dec 09 '21 at 07:09

score 0 · Answer 2 · answered Jan 09 '18 at 17:50

Using Cursor:

CREATE TABLE t (id int)
INSERT INTO t
VALUES(1)

INSERT INTO t
VALUES(2)

INSERT INTO t
VALUES(3)

INSERT INTO t
VALUES(4) 

DECLARE @actual int; 
DECLARE @last int;
DECLARE @sub int; 

SET @last = 0; 

DECLARE sub_cursor CURSOR FOR
    SELECT *
    FROM t OPEN sub_cursor 
    FETCH NEXT
    FROM sub_cursor INTO @actual; 

WHILE @@FETCH_STATUS = 0 BEGIN
    SELECT @sub = @actual - @last print cast(@actual AS nvarchar) + '-' + cast(@last AS nvarchar) + '=' + cast(@sub AS nvarchar)
    SET @last = @actual 
    FETCH NEXT FROM sub_cursor INTO @actual; 
END

DROP TABLE t 
CLOSE sub_cursor; DEALLOCATE sub_cursor;

How can I subtract a previous row in sql?

2 Answers2

Linked