Numerando

Que a Codd le apetecía utilizar los conjuntos para definir su modelo relacional ya lo sabíamos o estáis a punto de saberlo. Una característica importante de los conjuntos, matemáticamente hablando, es su ausencia de orden. Así, pues, partiendo de esta sencilla tabla

create table tabla(

num integer primary key,

tipo char(1),

precio decimal(6,2)

)

la teoría nos dice que aunque obtengamos la siguiente salida

select * from tabla; 

num	tipo	precio
241	A	241,44
503	A	202,68
544	A	243,58
604	A	3,98
904	A	3,91
985	A	84,54
54	B	54,33
254	B	253,58
828	B	228,07
894	B	293,75
155	C	155,08
560	C	259,89
636	C	36,47
957	C	57,16

nada nos garantiza que la fila que vemos arriba, justo debajo de la cabecera, sea "la primera" fila, ni la siguiente "la segunda", ni... Por mucho que nos empeñemos en seguir los dictados de nuestra educación de primaria, "de izquierda a derecha y de arriba a abajo" no se aplica, aunque lo parezca. Otra cosa es que se requiera ordenar, los listados son para eso, y es muy normal algo así:

select * from tabla order by tipo,num;

num	tipo	precio
241	A	241,44
503	A	202,68
544	A	243,58
604	A	3,98
904	A	3,91
985	A	84,54
54	B	54,33
254	B	253,58
828	B	228,07
894	B	293,75
155	C	155,08
560	C	259,89
636	C	36,47
957	C	57,16

Pero hete aquí que, de repente, necesitamos algo más, queremos numerar las filas. El no va más. MySQL tiene unas peculiaridades que nos permiten hacer cosas así.

select @fila:=@fila+1 orden, tabla.*

from tabla,

(select @fila:=0) xx

order by tipo,num;

 

orden	num	tipo	precio
1	241	A	241,44
2	503	A	202,68
3	544	A	243,58
4	604	A	3,98
5	904	A	3,91
6	985	A	84,54
7	54	B	54,33
8	254	B	253,58
9	828	B	228,07
10	894	B	293,75
11	155	C	155,08
12	560	C	259,89
13	636	C	36,47
14	957	C	57,16

O sea, hemos añadido una columna "orden" que numera secuencialmente las filas. Esto se consigue mediante una variable de usuario, @fila. En primer lugar debemos inicializarla, lo que se consigue con la subconsulta "xx" que se utiliza como tabla temporal en el from. Para los muy programadores hay una sintaxis alternativa que igual les haga sentir más cómodos, pero los resultados son idénticos:

set @fila:=0;

select @fila:=@fila+1 orden, tabla.*

from tabla

order by tipo,num;

Pero, ¿y si quisiéramos más? ¿Y si nuestro problema es que queremos que el contador se reinicie para cada tipo diferente?

orden	num	tipo	precio
1	241	A	241,44
2	503	A	202,68
3	544	A	243,58
4	604	A	3,98
5	904	A	3,91
6	985	A	84,54
1	54	B	54,33
2	254	B	253,58
3	828	B	228,07
4	894	B	293,75
1	155	C	155,08
2	560	C	259,89
3	636	C	36,47
4	957	C	57,16

La solución es la siguiente:

select if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) orden, num, @tipo:=tipo tipo, precio

from tabla,

(select @fila:=0) xx,

(select @tipo:='') tt

order by tipo,num;

MySQL evalua las columnas de la parte select de izquierda a derecha, lo que permite que la condición if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) añada 1 al contador si el tipo de la fila anterior (@tipo) es igual al de la fila que se esté tratando, y lo reinicie a 1 si son distintos. A continuación, se actualiza @tipo al valor de la fila actual y se deja preparado para la siguiente.

Para aclararnos, el orden de las columnas y las asignaciones a las variables de usuario es algo así como si hubiéramos escrito en algo parecido a PHP:

$fila = 0; $tipo='';

while ($tupla = obtenertupla() {

   if ($tipo == $tupla['tipo']) $fila++;

   else $fila=1;

   echo $fila . ":" . $tupla['num']. ":" . $tupla['tipo']. ":" . $tupla['precio'];

   $tipo =  $tupla['tipo']

}

Dicho de otra forma, si hubiéramos reordenado las columnas como

select @tipo:=tipo tipo, if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) orden, num, precio from...

ya no hubiera funcionado. ¿Qué quieres reordenar las columnas? Vale.

select tipo, orden, num, precio

from

(

select if (@tipo=tipo,(@fila:=@fila+1),@fila:=1) orden, num, @tipo:=tipo tipo, precio

from tabla,

(select @fila:=0) xx,

(select @tipo:='') tt

order by tipo,num

) zz;

tipo	orden	num	precio
A	1	241	241,44
A	2	503	202,68
A	3	544	243,58
A	4	604	3,98
A	5	904	3,91
A	6	985	84,54
B	1	54	54,33
B	2	254	253,58
B	3	828	228,07
B	4	894	293,75
C	1	155	155,08
C	2	560	259,89
C	3	636	36,47
C	4	957	57,16

En cualquier caso, aparte de las mencionadas variables de usuario y sus peculiaridades, hay que echarle un vistazo a los requisitos y uso de las subconsultas como tablas temporales.

Referencias adicionales: Jimmy's blog,  Cambrico.net.