On 1-11-2014 15:30, Igor Tandetnik wrote:
On 11/1/2014 8:24 AM, Clemens Ladisch wrote:
Use correlated subqueries:
SELECT Disruption_id,
(SELECT MIN(Time_Event)
FROM Table_1 AS T2
WHERE T2.Disruption_id = T1.Disruption_id
) AS Start_Time,
(SELECT MIN(Time_Event)
FROM Table_1 AS T2
WHERE T2.Disruption_id = T1.Disruption_id
AND T2.State = 'Closed'
) AS End_Time
FROM (SELECT DISTINCT Disruption_id
FROM Table_1) AS T1
Another, related technique:
select Disruption_id,
min(case State when 'Active' then Time_Event else null end) Start_Time,
min(case State when 'Closed' then Time_Event else null end) End_Time
from Table_1 group by Disruption_id;
Might be faster as it doesn't require joins and works in a single pass.
Both queries would benefit from an index on Disruption_id.
Is the 'else null' part needed??, or can it be deleted giving:
select Disruption_id,
min(case State when 'Active' then Time_Event end) Start_Time,
min(case State when 'Closed' then Time_Event end) End_Time
from Table_1 group by Disruption_id;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
