3

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?

xordon
  • 5,523
  • 4
  • 18
  • 26

1 Answers1

5

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
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Since the real data has a number of different "from" id's I changed the query to use OVER (PARTITION BY "from" ORDER BY timestamp). Other than that this is exactly what I needed. Now I just have to upgrade postgres 8.3 to 8.4 so that I can actually run this windowing function. – xordon Sep 21 '10 at 23:39