> ID | TimeStamp | Status > ---------------------------------------------------- > 0 | 2012-07-24 22:23:00 | status1 > 1 | 2012-07-24 22:23:05 | status1 > 2 | 2012-07-24 22:23:10 | status2 > 3 | 2012-07-24 22:23:16 | status2 > 4 | 2012-07-24 22:23:21 | status2 > 5 | 2012-07-24 22:23:26 | status2 > 6 | 2012-07-24 22:23:32 | status2 > 7 | 2012-07-24 22:23:37 | status3 > 8 | 2012-07-24 22:23:42 | status3 > 9 | 2012-07-24 22:23:47 | status3 > > What I want as result is > ID | Begin | End | Status > ------------------------------------------------------------------------------- > 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 > 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 > 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 >
> > Hmmm, the ID in the result bears virtually no relation to the ID in the data. > Is that intentional? > > Anyhow, some of what you want could come from > > select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data group > by Status order by Status > Hi Gerry, thank you but this won't work if the table look like this: ID | TimeStamp | Status ---------------------------------------------------- 0 | 2012-07-24 22:23:00 | status1 1 | 2012-07-24 22:23:05 | status1 2 | 2012-07-24 22:23:10 | status2 3 | 2012-07-24 22:23:16 | status2 4 | 2012-07-24 22:23:21 | status1 5 | 2012-07-24 22:23:26 | status1 6 | 2012-07-24 22:23:32 | status2 7 | 2012-07-24 22:23:37 | status3 8 | 2012-07-24 22:23:42 | status3 9 | 2012-07-24 22:23:47 | status3 Than you get: ID | Begin | End | Status ------------------------------------------------------------------------------- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:26 | status1 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 But it should be: ID | Begin | End | Status ------------------------------------------------------------------------------- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 0 | 2012-07-24 22:23:10 | 2012-07-24 22:23:16 | status2 1 | 2012-07-24 22:23:21 | 2012-07-24 22:23:26 | status1 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 You are right the ID column is not relevant. You can ignore them. regards Steffen Mangold
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
