Detecting changes between rows with same ID

Posted by Noah on Stack Overflow See other posts from Stack Overflow or by Noah
Published on 2010-03-22T15:45:32Z Indexed on 2010/03/22 16:01 UTC
Read the original article Hit count: 289

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about database