Re: [sqlite] Group contiguous rows (islands)

2017-02-16 Thread Rossel, Jonathan
@ Pasma and Hainaut, Thanks again, that looks promising ! Jonathan Message: 42 Date: Wed, 15 Feb 2017 21:10:10 +0100 From: "E.Pasma" <pasm...@concepts.nl> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Group contiguous rows (islands)

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread E.Pasma
Jean-Luc Hainaut: On 15/02/2017 18:34, E.Pasma wrote: Hello, the query below is simpler. May be slower. But looks pretty relational. Thanks, E Pasma. create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Jean-Luc Hainaut
On 15/02/2017 18:34, E.Pasma wrote: Hello, the query below is simpler. May be slower. But looks pretty relational. Thanks, E Pasma. create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread E.Pasma
15 feb 2017, Jean-Luc Hainaut: You could try this, inspired by classic algorithms of temporal databases: create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'), (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Rossel, Jonathan
@ Simon, Thanks for the input ! I was afraid someone was going to mention the dreaded recursive CTEs. Jonathan *** This e-mail message is intended only for the addressee(s) and contains information which may be

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Rossel, Jonathan
Jonathan -- Message: 79 Date: Wed, 15 Feb 2017 11:16:24 +0100 From: Clemens Ladisch <clem...@ladisch.de> To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Group contiguous rows (islands) Message-ID: <d689fbd5-0f4f-34ac-db98-72872d7a7...@ladisch.de> Content-Type: text/plain; ch

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Simon Slavin
On 15 Feb 2017, at 11:58am, Petite Abeille wrote: > On Feb 15, 2017, at 11:16 AM, Clemens Ladisch wrote: > >> SQLite does not have windowing functions. > > A continuous/continual tragedy indeed :| Windowing breaks the philosophy behind SQL.

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Petite Abeille
> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch wrote: > > SQLite does not have windowing functions. A continuous/continual tragedy indeed :| Still, worthwhile mentioning The Tabibitosan Method, for reference purpose: http://www.orchestrapit.co.uk/?p=53

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Jean-Luc Hainaut
You could try this, inspired by classic algorithms of temporal databases: create table T(date integer,test char(12)); insert into T values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'), (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb'); create table TT(seq integer not null

Re: [sqlite] Group contiguous rows (islands)

2017-02-15 Thread Clemens Ladisch
Rossel, Jonathan wrote: > Other database engines have solutions for this task (like windowing in > postgre) but I wonder if there is an efficient recipe in SQLite. SQLite does not have windowing functions. So the most efficient method would be to read the data with a simple ORDER BY, and do the

[sqlite] Group contiguous rows (islands)

2017-02-15 Thread Rossel, Jonathan
Dear all, I need to perform a kind of partial GROUP BY to determine the beginnings and ends of sets of identical data. I can't use a full GROUP BY because these sets can be repeated and their repetition must be conserved. Other database engines have solutions for this task (like windowing in