Detecting changes between rows with the same identifier

Question

I have a table containing some names and associated identifiers, as well as a snapshot:

snapshot, systemid, name[, some, other, columns]

I need to identify all the unique ones namethat a systemidhad in all snapshots, but only where there was at least one change.

For example, with data:

'DR1', 0, 'MOUSE_SPEED'
'DR1', 1, 'MOUSE_POS'
'DV8', 0, 'MOUSE_BUTTONS'
'DV8', 1, 'MOUSE_POS'
'DR6', 0, 'MOUSE_BUTTONS'
'DR6', 1, 'MOUSE_POS'
'PP2', 0, 'MOUSE_SPEED'
'PP2', 1, 'MOUSE_POS'

... I need a query that will return (in any order):

0, 'MOUSE_SPEED'
0, 'MOUSE_BUTTONS'

In addition, it would be useful to have a reverse - a list systemidthat remained stable in everything snapshot (that is, when it namenever changed).

I am using PostgreSQL v8.4.2.

EDIT: Updated to reflect comments (sorry for the original post, other than perfect, I'm new here!).

Answer 1

SQL Server, SQL Server. postgresql.

SQL

SELECT  DISTINCT t1.id, t1.name
FROM    @Table t1
        INNER JOIN (
          SELECT  t.id 
          FROM    (
                    SELECT  DISTINCT id, name
                    FROM    @Table
                  ) t
          GROUP BY t.id 
          HAVING COUNT(*) > 1
        ) t2 ON t2.id = t1.id

DECLARE @Table TABLE (snapshot INTEGER, id INTEGER, name VARCHAR(32))

INSERT INTO @TABLE
SELECT 1, 0, 'MOUSE_SPEED'
UNION ALL SELECT 1, 1, 'MOUSE_POS'
UNION ALL SELECT 1, 2, 'KEYBOARD_STATE'
UNION ALL SELECT 2, 0, 'MOUSE_BUTTONS'
UNION ALL SELECT 2, 1, 'MOUSE_POS'
UNION ALL SELECT 2, 2, 'KEYBOARD_STATE'
UNION ALL SELECT 3, 0, 'MOUSE_SPEED'
UNION ALL SELECT 3, 1, 'MOUSE_POS'
UNION ALL SELECT 3, 2, 'KEYBOARD_STATE'

Answer 2

PostgreSQL EXCEPT, , , , MINUS (, Oracle), , - ?

select id, name
from some_table
where snapshot = '1' and id in ('1', '2', '0')
except
select id, name
from some_table
where snapshot = '2' and id in ('1', '2', '0')

, EXCEPT s, , ():

for i = 1 to maX(snapshot)-1 loop
    results := diff_query(i, i+1)  //the query above, but inside a procedure or something
    forall records in results loop
        /* do your processing  here */
    end loop
end loop

, set .

Answer 3

select distinct s1.snapshot, s1.id, s1.name from snapshot s1, snapshot s2   
where s1.snapshot != s2.snapshot   
and s1.id = s2.id   
and s1.name != s2.name

Answer 4

:

SELECT t1.snapshot, t1.systemid
FROM table t1
GROUP BY t1.snapshot, t1.systemid
HAVING min(t1.name) <> max(t1.name)

,

,

SELECT t1.snapshot, t1.systemid
FROM table t1
GROUP BY t1.snapshot, t1.systemid
HAVING min(t1.name) = max(t1.name)

Joining values ​​to the first or last query can be done using a combined subquery or a correlated subquery

Joined (example with changed names)

SELECT t2.snapshot, t2.systemid, t2.name
FROM table t2
     JOIN (
           SELECT snapshot, systemid
           FROM table 
           GROUP BY snapshot, systemid
           HAVING min(name) <> max(name) ) t1
     ON t2.snapshot = t1.snapshot AND t2.systemid = t1.systemid

Correlated (example with names that remain the same)

SELECT t2.snapshot, t2.systemid, t2.name
FROM table t2
WHERE t2.name IN (
           SELECT t1.name
           FROM table t1
           WHERE t2.snapshot = t1.snapshot AND t2.systemid = t1.systemid
           GROUP BY t1.name
           HAVING COUNT(DISTINCT t1.name) = 1 ) 

If you need a snapshot for the reverse request, then

SELECT DISTINCT t2.systemid, t2.name

and stay the same.

Requests are not checked, but I hope the approaches are clear