Hi Clemens, first thank you for your comments.
> > It works for what you've asked. Perhaps you should not have kept your actual > requirements a secret. > Sorry for being unclear! :( Hope my English is understandable (I'm from Germany). > > So you want to group only consecutive events with identical statuses, where > "consecutive" is defined on the ordering of the timestamps? > > Then why do you write them? (And why are there duplicates in your last > example?) > You are right I only want consecutive events of same status. Here more a clear example. This is the table: TimeStamp | Status ------------------------------------------------- 2012-07-24 22:23:00 | status1 2012-07-24 22:23:05 | status1 2012-07-24 22:23:10 | status2 2012-07-24 22:23:16 | status2 2012-07-24 22:23:21 | status1 2012-07-24 22:23:26 | status1 2012-07-24 22:23:32 | status2 2012-07-24 22:23:37 | status3 2012-07-24 22:23:42 | status3 2012-07-24 22:23:47 | status3 This is what I want: Begin | End | Status --------------------------------------------------------------------------- 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 2012-07-24 22:23:10 | 2012-07-24 22:23:16 | status2 2012-07-24 22:23:21 | 2012-07-24 22:23:26 | status1 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 > > Well, try this: > > SELECT TimeStamp AS Begin, > (SELECT MAX(TimeStamp) > FROM Data AS same > WHERE same.Status = ou.Status > AND same.TimeStamp >= ou.TimeStamp > AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp) > FROM Data AS next > WHERE next.TimeStamp > ou.TimeStamp > AND next.Status <> ou.Status), > '9999')) > ) AS End, > Status > FROM Data AS ou > WHERE Status IS NOT (SELECT Status > FROM (SELECT Status, > MAX(prev.TimeStamp) > FROM Data AS prev > WHERE prev.TimeStamp < ou.TimeStamp)) > Thank you I try this. > > (And it might be easier and faster to just query the events ordered by > timestamp, and aggregate statuses by hand in your code.) > Hm ok I make some test. I think you can be right that in-code aggregation is faster that subquerys. Regards, Steffen _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
