On 1 Nov 2014, at 10:19am, Rob Willett <rob.sql...@robertwillett.com> wrote:
> I struggle when I try to pull it all together so that I have one piece of SQL > that does all the work. You want to identify the first Active and the first Closed for each Disruption_id. Your biggest problem is that, as you describe clearly and usefully, your data is not clean and orderly. You have too many combinations and possibilities to be able to get SQlite to do all the work. You might be able to get SQLite to do some of the work by using some complicated commands but I suspect that you'll be better off by using your programming language to do some of the work. Do SELECT * FROM Table_1 ORDER BY Disruption_id,Time_Event Then spot a change in Disruption_id, and with each Disruption_id spot the first Active and the first Closed. You can come up with your own ideas about what to use when either status is missing. This combination of SQLite and your programming language will lead to a fast result from code which is easy to understand and debug. To make the SELECT run faster you'll want to create an index on (Disruption_id,Time_Event). Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
