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] Index without backing table

2014-11-01 Thread James K. Lowden
On Sat, 01 Nov 2014 11:06:51 +0200 Paul wrote: > Would be nice to have ability to store both key and payload in the > index. (Let's call it index-only table) > This could be a feature that sets some limitations on a table, like > being unable to have more than one index or

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] sqlite_column_table_name() and table alias name

2014-11-01 Thread Simon Slavin
On 1 Nov 2014, at 12:29pm, Jose F. Gimenez wrote: > But if I need the column's fullname, I get: > > messages.subject, people.name, people.name > > And yes, I know that I can specify an alias for those columns which could be > ambiguous. That is "sender.name AS

Re: [sqlite] sqlite_column_table_name() and table alias name

2014-11-01 Thread Jose F. Gimenez
Simon, thanks for replying. Specify which names you want SQLite to use: SELECT a, b, table2.c AS table2, alias.c AS alias FROM table1 LEFT JOIN table2 ON ... LEFT JOIN table2 AS alias ON ... However, I strongly advise against having one string which is both a table name and a column name

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,

Re: [sqlite] Index without backing table

2014-11-01 Thread Paul
Hi Edward, To be specific about my case: What I really wanted is to have Key -> Value mapping (B-tree). I love how SQLite can handle compound keys. So for example, if my key is a compound key (A, B), I can query values by A as well as by (A, B). This is very useful. Imagine a case when Key