Re: [sqlite] 2 consecutive rises in value

> On Oct 19, 2016, at 11:40 PM, Bart Smissaert wrote: > > ID 2 for example shouldn't be selected. Hmmm? ID 2 has two ‘raises' as per your definition: 39 > 42 > 43 and 42 > 43 > 45 2 2004-06-23 42 2 2006-12-28 39 2 2007-10-09 42 2 2007-10-24 43 2 2009-06-17 45 2

Re: [sqlite] 2 consecutive rises in value

On 2016/10/19 4:53 PM, Bart Smissaert wrote: Have a table like this: create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE] INTEGER) with data like this: ID ISO8601_dateINT_VALUE 1 2016-01-01 10 1

Re: [sqlite] 2 consecutive rises in value

Looks very nice and a lot faster, but I get more ID's than I should. With this data for example: ID ISO8601_DATE INT_VALUE 2 2004-06-23 42 2 2006-12-28 39 2 2007-10-09 42 2 2007-10-24 43 2 2009-06-17 45 2 2015-09-09 36 3 2004-05-05 46 3 2005-05-24 43 3

Re: [sqlite] 2 consecutive rises in value

> On Oct 20, 2016, at 12:23 AM, Bart Smissaert wrote: > > Ah, yes, sorry, I needed actually drops and was looking at that. Just for fun, here is one using recursion: with DataSet as ( select 1 as id, 2 as key, '2004-06-23' as date, 42 as value union all select 2

Re: [sqlite] 2 consecutive rises in value

Thanks, will try that as well. RBS On Thu, Oct 20, 2016 at 12:16 AM, Petite Abeille wrote: > > > On Oct 20, 2016, at 12:23 AM, Bart Smissaert > wrote: > > > > Ah, yes, sorry, I needed actually drops and was looking at that. > > Just for fun,

Re: [sqlite] 2 consecutive rises in value

> It has three consecutive drops, no? E.g. 47 < 46 < 37 That is 3 values, but only 2 drops. RBS On Thu, Oct 20, 2016 at 1:07 AM, Petite Abeille wrote: > > > On Oct 20, 2016, at 1:58 AM, Bart Smissaert > wrote: > > > > I worked it round to

Re: [sqlite] 2 consecutive rises in value

Ah, yes, sorry, I needed actually drops and was looking at that. Will work it round. RBS On Wed, Oct 19, 2016 at 11:06 PM, Petite Abeille wrote: > > > On Oct 19, 2016, at 11:40 PM, Bart Smissaert > wrote: > > > > ID 2 for example shouldn't

Re: [sqlite] 2 consecutive rises in value

I worked it round to get 3 consecutive drops: WITH C1(ID, DT, V) AS ( SELECT ID, ISO8601_DATE, MIN(INT_VALUE) FROM TABLE1 GROUP BY ID, ISO8601_DATE ) SELECT DISTINCT A.ID FROM C1 AS A JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1 AS X

Re: [sqlite] 2 consecutive rises in value

> On Oct 20, 2016, at 1:58 AM, Bart Smissaert wrote: > > I worked it round to get 3 consecutive drops: > > but I must have done something wrong as it picked 62 up from this: It has three consecutive drops, no? E.g. 47 < 46 < 37 Example: with DataSet as ( select

Re: [sqlite] 2 consecutive rises in value

> and do NOT reverse comparison operators in the final WHERE clause. Yes, thank, that was it. RBS On Thu, Oct 20, 2016 at 4:40 AM, Cezary H. Noweta wrote: > Hello, > > On 2016-10-20 01:58, Bart Smissaert wrote: > >> I worked it round to get 3 consecutive drops: >> > >

Re: [sqlite] 2 consecutive rises in value

Hello, On 2016-10-20 01:58, Bart Smissaert wrote: I worked it round to get 3 consecutive drops: [...] JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1 AS X WHERE X.ID = A.ID AND X.DT > A.DT) JOIN C1 AS C ON C.ID = A.ID AND C.DT = (SELECT MAX(X.DT) FROM

Re: [sqlite] Introducing Bedrock: SQLite for the Enterprise

So am I to understand you just "server-ized" SQLite with Bedrock? Looks rather interesting. I was just talking to my IT manager about how I can take something like a SQLite backup and put it somewhere else so it'll eventually get to tape, but, if I run a node on my local machine, run one on the

[sqlite] 2 consecutive rises in value

Have a table like this: create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE] INTEGER) with data like this: ID ISO8601_dateINT_VALUE 1 2016-01-01 10 1 2016-01-28 9 1 2016-03-05

Re: [sqlite] 2 consecutive rises in value

Write a procedural program... ;) In SQL it requires a triple self join similar to: Select t1.id from table1 t1, table1 t2, table1 t3 where t2.id=t1.d and t1.value < t2.value and t2.date = (select min(date) from table1 where id =t.id and date > t1.date) and t3.id=t2.id and t2.value < t3.value

Re: [sqlite] 2 consecutive rises in value

Thanks for that, trying this out now. Must have done something wrong as for now I get too many ID's. My actual fields are somewhat different. It is indeed slow, something like 150 secs to process a table of 10 rows. This is with select distinct ID etc. To do this in code will be indeed a lot