CODEWITHSUNDEEP
sqlite
bjornasm
bjornasm

Reputation: 2318

Select for a change of values, and get the time of the last change

Sorry for the cumbersome title.

I have a table with the following headers in an sqlite database

id1 | id2 | unixtime

I want to record when id2 changes value:

id1 | id2 | unixtime
 A     1       2
 ..    ..      ..
 A     1       5
 A     2       8

Preferrably like so:

   id1 | id2 | from | to
    A     1      2    8
    A     2      8    -

I have a large number of id1 and id2, the change can happen from 0 (most likely) and up to 20-30 times. Can anyone point me in the right direction?

Upvotes: 0

Views: 23

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use windowed functions :

WITH cte AS (
  SELECT DISTINCT id1, id2,
         FIRST_VALUE(unixtime) OVER(PARTITION BY id1,id2 ORDER BY unixtime) AS f
  FROM tab
)
SELECT id1, id2, f AS "from", COALESCE(LEAD(f) OVER(PARTITION BY id1 ORDER BY f),'-') AS "to"
FROM cte
ORDER BY f;

db<>fiddle demo

Upvotes: 1

Related Questions