jeudi 13 août 2015

SQLite query to GROUP BY nearby row

I have a SQLite .db file that contains the Thread table that looks like this:

ThreadID  ClusterID
1         0
2         0
3         0
4         1
5         1
6         0
7         1
8         1
9         0
10        1

And I would like to GROUP BY the ClusterID by only with the nearby row. Output would be:

ThreadID  ClusterID
1         0     
4         1
6         0
7         1
9         0
10        1

Or ideally:

ThreadID  ClusterID ClusterSwitch
1         0         NO
2         0         NO
3         0         NO
4         1         YES
5         1         NO
6         0         YES
7         1         YES
8         1         NO
9         0         YES
10        1         YES

The whole design its to detect when a cluster switched from 0 to 1 and from 1 to 0

Thanks for your help it is really appreciated :) -Steve

Aucun commentaire:

Enregistrer un commentaire