Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Rob Willett
OK, thanks. A little more optimisation :) > On 1 Nov 2014, at 17:39, Igor Tandetnik wrote: > > On 11/1/2014 11:52 AM, Luuk wrote: >> Is the 'else null' part needed??, or can it be deleted > > Yes, it can be removed. CASE expression returns null when no case matches. > -- >

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Luuk
On 1-11-2014 18:39, Igor Tandetnik wrote: On 11/1/2014 11:52 AM, Luuk wrote: Is the 'else null' part needed??, or can it be deleted Yes, it can be removed. CASE expression returns null when no case matches. Thanks for this confirmation ;) ___

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Igor Tandetnik
On 11/1/2014 11:52 AM, Luuk wrote: Is the 'else null' part needed??, or can it be deleted Yes, it can be removed. CASE expression returns null when no case matches. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Luuk
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,

Re: [sqlite] Stuck and drowning trying to work out some SQL. - Thanks

2014-11-01 Thread Rob Willett
Simon, Ivar, Clemens, Aleksey, Thank you for taking the time to reply to a drowning man. I have been offered four lifebelts. I've tested three of them out and they all work slightly differently but I'm very happy to work through them and to see where the advantages and disadvantages of each of

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Igor Tandetnik
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

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Clemens Ladisch
Rob Willett wrote: > 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

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Aleksey Tulinov
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

Re: [sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Simon Slavin
On 1 Nov 2014, at 10:19am, Rob Willett 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

[sqlite] Stuck and drowning trying to work out some SQL.

2014-11-01 Thread Rob Willett
I have a SQL problem I’m trying to solve and realise its now gone wyyy beyond my meagre SQL talents. A bit of context The dataset I’m looking at looks at traffic problems. Every five mins I get a new dataset of traffic updates. Some of the new dataset is a continuation of the old data,