Your expected result does not make any logical algorithmic sense whatsoever.
--- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of Steffen Mangold > Sent: Friday, 23 November, 2012 02:02 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Status analyze with Sqlite > > > > > 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
