Longest period with negative temperature SQL query

I have 3 columns CITY, DATE, VALUEE(TEMPERATURE). 50 years of daily temperature. How to find longest period with negative temperature with query?

See file Temperature.txt

Best regards, Vlado

See file Temperature.txt

Selecting maximum and minimum in a subselect won't help I'm afraid, because there can be positive degrees inbetween. No clue.

Just an idea - hopefully self-explaining... Assuming you have your data already in a table "Temp":

drop table if exists TT;
create table TT like Temp;
ALTER TABLE `TT`
    ADD COLUMN `AI` INT NOT NULL AUTO_INCREMENT FIRST,
    ADD PRIMARY KEY (`AI`),
    ADD UNIQUE INDEX `UniqueKey` (`City`, `MeasurmentDate`);

insert into TT
(City,MeasurmentDate,Temp)
select * from Temp
order by City,MeasurmentDate;

set @T=0;
select max(ContinousColdDays) into @MaxContinousColdDays
from (
    select AI,City,MeasurmentDate,Temp
    ,if(Temp>=0,@T:=0,@T:=@T + if(Temp<0,1,0)) as ContinousColdDays
    from TT
    order by AI
) t;

select MeasurmentDate as FistColdDay
,date_sub(MeasurmentDate, INTERVAL @MaxContinousColdDays day) as LastColdDay
,@MaxContinousColdDays as MaxContinousColdDays
from (
    select AI,City,MeasurmentDate,Temp
    ,if(Temp>=0,@T:=0,@T:=@T + if(Temp<0,1,0)) as ContinousColdDays
    from TT
    order by AI
) t
where ContinousColdDays=@MaxContinousColdDays;

My last post was cuted... :-( Please take a look into the attachment...

You're a GENIUS, I don't understand very well QUERY, but it works.

Just one help more, how to query with one City data ?

Ex. WHERE City='London'