I have a table containing some names and their associated ID, along with a snapshot:
snapshot, id, name
I need to identify when a name has changed for an id between snapshots. For example, in the following data:
1, 0, 'MOUSE_SPEED'
1, 1, 'MOUSE_POS'
1, 2, 'KEYBOARD_STATE'
2, 0, 'MOUSE_BUTTONS'
2, 1, 'MOUSE_POS'
2, 2, 'KEYBOARD_STATE'
...the meaning of id 0 changed with snapshot 2, but the others remained the same. I'd like to construct a query that (ideally) returns:
1, 0, 'MOUSE_SPEED'
2, 0, 'MOUSE_BUTTONS'
I am using PostgreSQL v8.4.2.