show a start and end range grouped by states.

Just to clarify what you ask. Actually you are not just trying to group by estado , because if that is the case, your original query and result would be correct.

Rather, for the desired result, it is obvious that what you really want is to group by state changes . In other words, assuming that the records are sorted by numero , each time the value of the estado changes, this should be considered as the beginning of a new grouping of records.

Normally, this type of query is armed using window functions, which MySQL does not have. Even so, it seems that with the use of variables, it is possible to build a query that seems to be more efficient than expected, although it is complex:

set @groupId := 0;
select min(numero) as inicio,
       max(numero) as final,
       max(estado) as estado
  from (select numero,
               estado,
               (@groupId := @groupId + cambio_de_estado) as group_id
          from (select t1.numero,
                       t1.estado,
                       case when t1.estado = t2.estado
                            then 0 else 1 end as cambio_de_estado
                  from tbl t1
                  left join tbl t2 on t2.numero = t1.numero - 1
                 order by t1.numero
               ) t
        ) t
 group by group_id
 order by max(estado), group_id;

Result:

inicio   final   estado
  4        5       0
  1        3       1
  6        7       1

Explanation in stages

1. Identify where the intervals begin

This is achieved through the following query:

select t1.numero,
       t1.estado,
       case when t1.estado = t2.estado
            then 0 else 1 end as cambio_de_estado
  from tbl t1
  left join tbl t2 on t2.numero = t1.numero - 1
 order by t1.numero

Which returns the following result:

numero    estado     cambio_de_estado
  1         1              1
  2         1              0
  3         1              0
  4         0              1
  5         0              0
  6         1              1
  7         1              0

This makes a join with the previous record to determine if there is a state change between the 2 records. If there is, we return 1 in column cambio_de_estado to identify that this is the beginning of an interval.

2. Assign a unique id to each interval

This is achieved by making a cumulative sum, which can not be done in pure SQL with MySQL (window functions are needed for this), but it can be simulated with the help of a variable:

set @groupId := 0;
select numero,
       estado,
       (@groupId := @groupId + cambio_de_estado) as group_id
  from (select t1.numero,
               t1.estado,
               case when t1.estado = t2.estado
                    then 0 else 1 end as cambio_de_estado
          from tbl t1
          left join tbl t2 on t2.numero = t1.numero - 1
         order by t1.numero
       ) t;

Result:

numero    estado     group_id
  1         1           1
  2         1           1
  3         1           1
  4         0           2
  5         0           2
  6         1           3
  7         1           3

3. Group by interval

Once we finally have a unique id that we can use to identify each interval, it is trivial to use a GROUP BY to get the desired result:

set @groupId := 0;
select min(numero) as inicio,
       max(numero) as final,
       max(estado) as estado
  from (select numero,
               estado,
               (@groupId := @groupId + cambio_de_estado) as group_id
          from (select t1.numero,
                       t1.estado,
                       case when t1.estado = t2.estado
                            then 0 else 1 end as cambio_de_estado
                  from tbl t1
                  left join tbl t2 on t2.numero = t1.numero - 1
                 order by t1.numero
               ) t
        ) t
 group by group_id
 order by max(estado), group_id;

Result:

inicio   final   estado
  4        5       0
  1        3       1
  6        7       1