@ 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)
Message-ID: <55fa9699-22fe-4dd9-9b86-36a190485...@concepts.nl>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
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'),
>> (13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');
>>
>> select min(date) as fromdate, max(date) as enddate, test
>> from (--get closest preceeding different key
>> select t.*, max(t2.date) as key2
>> from t
>> left join t t2
>> on t2.date<t.date and t2.test<>t.test
>> group by t.date
>> )
>> group by key2
>
> Quite nice solution indeed!
> For those who may feel uncomfortable with outer joins, the from
> clause could be written as a subquery:
>
> from (select date, test, (select max(date)
> from t t2
> where t2.date < t.date
> and t2.test <> t.test)
> as key2)
>
> Thanks
>
> J-L
>
this way you may also try to optimise speed by using ORDER BY & LIMIT
1 instead of MAX
from (select date, test, (select t2.date
from t t2
where t2.date < t.date
and t2.test <> t.test
order by t2.date desc limit 1)
as key2
*******************************************************************************
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete
this
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.
Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
*******************************************************************************
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
