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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
