How do I group repeated values to find the start and end of series?

I have a table with the following开发者_StackOverflow中文版 data.

serial   date     flag
1        1 aug    1
2        2 aug    1
3        3 aug    0   
4        4 aug    0
5        5 aug    1
6        6 aug    1
7        7 aug    1

What I need is this result set:

from     to       flag
1-aug    2-aug    1
3-Aug    4-Aug    0
5-Aug    7-Aug    1

When I group on flag all 1 values are combined. Any suggestions?

---

an old question, i know, but this may still help someone...

to solve this, i have assumed that the date will be sequential. i have then joined the data, first with 'yesterday's' data and then with 'tomorrow's' data and taken those rows where the flag value has changed - this marks the start & end dates of a given series. to match the start dates with the end dates, i've ordered each series, assigned a counter and joined on this counter.

here's the code:

SET @cnt1 := 0;
SET @cnt2 := 0;

select firstdate, lastdate, startDates.flag from
(
    select  @cnt2 := @cnt2 + 1 as i, table1.date as firstdate, table1.flag from table1
    left join (
        select DATE_ADD(date, INTERVAL 1 DAY) as dateplus1, date, flag from table1
        ) plus1 on table1.date = plus1.dateplus1
    where table1.flag <> IFNull(plus1.flag,-1)
    order by table1.date
) startDates
inner join
(
    select @cnt1 := @cnt1 + 1 as i, table1.date as lastdate, table1.flag from table1
    left join (
        select DATE_ADD(date, INTERVAL -1 DAY) as dateminus1, date, flag from table1
        ) minus1 on table1.date = minus1.dateminus1
    where table1.flag <> IFNull(minus1.flag,-1)
    order by table1.date
) endDates on startDates.i = endDates.i
;

sql fiddle here: http://sqlfiddle.com/#!2/25594/1/2