On 01/11/14 12:19, Rob Willett wrote: Rob,
I’d like to produce an output table based on the above data set that looks a bit like this. Disruption_id | Start Time | End Time 1 | 1 | 4 2 | 1 | 5 3 | 1 | 2 4 | 2 | 4
What do you think about self-joining original table, then doing two mins() on Time_Event?
sqlite> SELECT d1.x, min(d1.y) AS start, min(d2.y) AS end FROM data AS d1 LEFT JOIN data AS d2 ON d1.x=d2.x AND d1.z='active' AND d2.z='closed' GROUP BY d1.x;
1|1|4 2|1|5 3|1|2 4|2|4 5|4| x - Disruption_id y - Time_Event z - State Hope this helps. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
