sql - grouping on status change

Question

I have sample like below and i want to group the data on data change.

My table:
id  Status  time
1   a      20/6/2015 8:00
2   a      20/6/2015 8:46
3   a      20/6/2015 9:00
4   b      20/6/2015 9:00
5   b      20/6/2015 9:16
6   a      20/6/2015 9:17
7   a      21/6/2015 9:20

Sample output:

Status  mintime          maxtime
a      20/6/2015 8:00   20/6/2015 9:00
b      20/6/2015 9:00   20/6/2015 9:16
a      20/6/2015 9:17   21/6/2015 9:20

Basically i want to group the data on status ,taking min and max time of data only if status change.

My problem, if i use grouping on status column then i will get as below Status mintime maxtime

a   20/6/2015 8:00  21/6/2015 9:20
b   20/6/2015 9:00  20/6/2015 9:16

I don't want the output like above which returns me 2 data.I want to group the data based on status change.

Answer 1

You can do this with the different-of-row number method. The difference of row_number() -- with particular arguments -- identifies groups. The rest is just aggregation:

select status, min(time), max(time)
from (select t.*,
             (row_number() over (order by time) -
              row_number() over (partition by status order by time)
             ) as grp
      from mytable t
     ) t
group by status, grp;