SQL - Agregar columna de la ultima variable de un grupo de datos

mysql> SELECT * FROM tabla;

+-------+-----------+

| serie | secuencia |

+-------+-----------+

| A     |         1 |

| A     |         8 |

| B     |         1 |

| B     |         3 |

| B     |         6 |

| C     |         1 |

| C     |         2 |

| C     |         3 |

| C     |         4 |

+-------+-----------+

9 rows in set (0.00 sec)

mysql> SELECT serie, MAX(secuencia) max_secuencia

    -> FROM tabla

    -> GROUP BY serie;

+-------+---------------+

| serie | max_secuencia |

+-------+---------------+

| A     |             8 |

| B     |             6 |

| C     |             4 |

+-------+---------------+

3 rows in set (0.00 sec)

mysql> SELECT tabla.serie, tabla.secuencia, T.max_secuencia

    -> FROM tabla

    -> INNER JOIN

    -> (

    ->    SELECT serie, MAX(secuencia) max_secuencia

    ->    FROM tabla

    ->    GROUP BY serie

    -> ) T on T.serie = tabla.serie;

+-------+-----------+---------------+

| serie | secuencia | max_secuencia |

+-------+-----------+---------------+

| A     |         1 |             8 |

| A     |         8 |             8 |

| B     |         1 |             6 |

| B     |         3 |             6 |

| B     |         6 |             6 |

| C     |         1 |             4 |

| C     |         2 |             4 |

| C     |         3 |             4 |

| C     |         4 |             4 |

+-------+-----------+---------------+

9 rows in set (0.00 sec)