SELECT rows while performing operation for each row with previous one as argument

Question

Let's say I have a table of something like:

+-------+
| FIELD |
+-------+
| 1000  |
| 1200  |
| 1300  |
| 900   |
| 1400  |
+-------+

How can I perform SELECT query to retrieve rows 0..N, but instead of getting their values, get arithmetical difference with each row's previous row, i. e. RESULT(N) = ROW(N) - ROW(N-1) ? I expect to get something like:

+--------------+
| RESULT       |
+--------------+
| (EMPTY OR 0) |
| 200          |
| 100          |
| -400         |
| 500          |
+--------------+

I'm using DB2. It will be really cool if you provide me with some portable response that is not specific to a particular SQL DBMS.

Thanks in advance!

@ThitLwinOo: no, got no primary keys yet. – Andrew Dunai Mar 26 '14 at 17:51 — Andrew Dunai, Mar 26 '14 at 17:51

score 2 · Accepted Answer · answered Mar 26 '14 at 17:50

2

What you need is LAG function. it exists in multiple databases including db2

Syntax would be something like this

SELECT CASE WHEN LAG(MyField, 1) OVER ( PARTITION BY MyID ORDER BY SomeThing ) IS NULL THEN 0
            ELSE MyField - LAG(MyField, 1) OVER ( PARTITION BY MyID ORDER BY SomeThing )
       END AS Result
    FROM MyTable

Documentation on LAG from IBM

answered Mar 26 '14 at 17:50

1

Readers should understand this answer is platform specific, and applies only to DB2 for LUW, not z/OS or IBM i. – WarrenT Mar 31 '14 at 13:49
Readers should also be aware that the sample database from the question has no column by which to order; there is therefore no guaranteed single set of results returned. – Clockwork-Muse Apr 01 '14 at 13:35

SELECT rows while performing operation for each row with previous one as argument

1 Answers1