SQL Find difference between previous and current row

Question

I am trying to find the difference between the current row and the previous row. However, I am getting the following error message:

The multi-part identifier "tableName" could not be bound.

Not sure how to fix the error.

Thanks!

Output should look like the following:

columnOfNumbers     Difference
      1               NULL
      2               1
      3               1
      10              7
      12              2
      ....            ....

Code:

USE DATABASE;

WITH CTE AS 
(SELECT 
    ROW_NUMBER() OVER (PARTITION BY tableName ORDER BY columnOfNumbers) ROW,
    columnOfNumbers
    FROM tableName)
SELECT
    a.columnOfNumbers
FROM
    CTE a
    LEFT JOIN CTE b
    ON a.columnOfNumbers = b.columnOfNumbers AND a.ROW = b.ROW + 1

Which database are you using? – Joseph B Apr 21 '14 at 15:18 — Joseph B, Apr 21 '14 at 15:18
You have to partition by a column name. – Dan Bracuk Apr 21 '14 at 15:20 — Dan Bracuk, Apr 21 '14 at 15:20
can just omit the 'partition by' clause. – Karl Kieninger Apr 21 '14 at 15:30 — Karl Kieninger, Apr 21 '14 at 15:30

score 47 · Answer 1 · edited Jun 20 '20 at 09:12

47

If you in SQL Server 2012+ You can use LAG.

 SELECT columnOfNumbers
       ,columnOfNumbers - LAG(columnOfNumbers, 1) OVER (ORDER BY columnOfNumbers)
   FROM tableName

Note: The optional third parameter of LAG is:

default

The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 21 '14 at 15:33

Karl Kieninger

8,841
2
33
49

Nice solution, but it give the wrong result for the first row, 1 instead of null. – wvdz Apr 21 '14 at 15:37
5

Ah, right, I didn't read the desired sample output carefully enough. If we omit the 3rd paramteer in LAG that is default 0 it will instead give a null int he first row. – Karl Kieninger Apr 21 '14 at 15:52
1

I compared this to the other posted solution with ROW_NUMBER, but to have the same data available, I added another LAG to also get primary key of previous record, to then do a join. This method took less than half the time compared to using ROW_NUMBER join. – Inrego Jun 01 '22 at 10:09

score 30 · Accepted Answer · answered Apr 21 '14 at 15:25

See sqlFiddle

;WITH tblDifference AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers 
    FROM tableName
)

SELECT cur.columnOfNumbers, cur.columnOfNumbers - previous.columnOfNumbers
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1

Gordon Linoff · Answer 3 · 2014-04-21T15:36:25.830

2

I don't think you need the partition by statement:

WITH CTE AS (
      SELECT ROW_NUMBER() OVER (ORDER BY columnOfNumbers) as ROW,
             columnOfNumbers
      FROM tableName
     )
SELECT a.columnOfNumbers, a.columnOfNumbers - b.columnOfNumbers
FROM CTE a LEFT JOIN
     CTE b
     ON a.ROW = b.ROW + 1;

If you do need it, you should put in a column name as opposed to a table name.

edited Apr 21 '14 at 15:36

answered Apr 21 '14 at 15:20

Gordon Linoff

1,242,037
58
646
786

I tried this query, but it returns NULLS in the second column. Any suggestions? – ChaseHardin Apr 21 '14 at 15:25
@user3356901 . . . It returned `NULL`s because I left in your condition `a.columnOfNumbers = b.columnOfNumbers`. – Gordon Linoff Apr 21 '14 at 15:36

SQL Find difference between previous and current row

3 Answers3

Linked

Related