CODEWITHSUNDEEP
sqlpostgresql
xordon
xordon

Reputation: 5651

How to select specific changes using windowing functions in postgres

I have a table with some foreign keys, I need to get a report of when these keys change.

from | to | timestamp
   1 |  2 | 0000
   1 |  2 | 0001
   1 |  2 | 0002
   1 |  3 | 0003
   1 |  3 | 0004
   1 |  2 | 0005

SELECT from,to,FIRST(timestamp) FROM table GROUP BY from,to;

from | to | timestamp
   1 |  2 | 0000
   1 |  3 | 0003

I can do Group By to get the first two, transitions but it groups the third in with the first and I am not able to see it when it goes back.

I would like to make a query that gets following:

from | to | timestamp
   1 |  2 | 0000
   1 |  3 | 0003
   1 |  2 | 0005

Is it possible?

Upvotes: 3

Views: 870

Answers (1)

Mark Byers
Mark Byers

Reputation: 838096

In PostgreSQL 8.4 you can use the window function LAG to access the previous row and compare it to see if it has the same values of "from" and "to":

SELECT "from", "to", timestamp
FROM
(
    SELECT
        "from",
        "to",
        timestamp,
        LAG(("from", "to")) OVER (ORDER BY timestamp) AS prev
    FROM Table1
) T1
WHERE ("from", "to") IS DISTINCT FROM prev

Result:

from  to    timestamp
1     2     0000        
1     3     0003        
1     2     0005

Upvotes: 5

Related Questions