Equivalente del ROWNUM de Oracle en MySQL

La pseudocolumna ROWNUM sirve, en Oracle, fundamentalmente para dos cosas:

  • Numerar por órden de visualización los registros recuperados por una consulta.
  • Limitar el número de registros devueltos por una consulta

Para realizar lo segundo en MySQL, la cláusula LIMIT es mucho más simple y efectiva que el método para limitar con ROWNUM en Oracle, sin embargo lo primero, es decir, mostrar un contador de registros en MySQL no tiene un equivalente directo, aún así, se puede emular de la siguiente forma:

Pongamos la siguiente consulta, que devuelve los registros de una tabla de ejemplo:

mysql> SELECT * FROM frutas;
+-----------+----------+
| nombre    | color    |
+-----------+----------+
| fresa     | rojo     |
| platano   | amarillo |
| manzana   | verde    |
| uva       | verde    |
| pera      | verde    |
| mandarina | naranja  |
| melocoton | marron   |
| limon     | amarillo |
+-----------+----------+
8 rows in set (0,00 sec)

Utilizando variables podemos conseguir mostrar el número de fila en MySQL:

SELECT @rownum:=@rownum+1 AS rownum, frutas.*
FROM (SELECT @rownum:=0) r, frutas;
mysql> SELECT @rownum:=@rownum+1 AS rownum, frutas.*
    -> FROM (SELECT @rownum:=0) r, frutas;
+--------+-----------+----------+
| rownum | nombre    | color    |
+--------+-----------+----------+
|      1 | fresa     | rojo     |
|      2 | platano   | amarillo |
|      3 | manzana   | verde    |
|      4 | uva       | verde    |
|      5 | pera      | verde    |
|      6 | mandarina | naranja  |
|      7 | melocoton | marron   |
|      8 | limon     | amarillo |
+--------+-----------+----------+
8 rows in set (0,00 sec)

Desgraciadamente no es posible usar variables dentro del código de las vistas, por lo que si intentamos crear una vista con el código anterior, nos devolverá un error:

mysql> CREATE VIEW vw_frutas AS SELECT @rownum:=@rownum+1 AS rownum, frutas.*
    ->  FROM (SELECT @rownum:=0) r, frutas;
ERROR 1351 (HY000): View's SELECT contains a variable or parameter

Gracias a Albert por la idea del post ;-)

Comentarios

[...] 1. Equivalente del ROWNUM de Oracle en MySQL [...]

buenas!!! o no tanto...:(
"<<Desgraciadamente no es posible usar variables dentro del código de las vistas...>>" que es exactamente lo que estoy qeriendo hacer...:(
No habra alguna otra forma?

esto es lo que iba entre las comillas del comentario anterior..."Desgraciadamente no es posible usar variables dentro del código de las vistas"
maldito error #1351!!!

Seguramente con procedimientos almacenados podrías resolverlo: http://dev.mysql.com/doc/refman/5.0/es/stored-procedures.html

Por ejemplo, creando un procedimiento o una función que haga la SELECT que tu quieras, y acepte como argumento el valor del where, devolviendo los datos que necesitas.

gracias, antes tenia que poner los datos en una tabla temporal y poner un autoincrementable, ahora me ahorraste unos pasos con esto que presentas, gracias !!!

oye, una pregunta crees que se pueda meter esto en una variable para despues recorrerla y saber las posiciones, por ejemplo usando INTO, y despues en un while obtener los datos uno por uno, para realizar mas operaciones !!!! se ve que eres bueno en las consultas, gracias

skuarch, sí que podrías utilizarlo con un INTO, por ejemplo:

<code>mysql> SELECT @rownum:=@rownum+1 AS id, nombre, color FROM (SELECT @rownum:=0) r, frutas;
+------+---------+----------+
| id | nombre | color |
+------+---------+----------+
| 1 | fresa | rojo |
| 2 | platano | amarillo |
+------+---------+----------+</code>

<code>INSERT INTO frutas_id select @rownum:=@rownum+1 AS id, nombre, color FROM (SELECT @rownum:=0) r, frutas;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

<code>SELECT * FROM frutas_id;
+------+---------+----------+
| id | nombre | color |
+------+---------+----------+
| 1 | fresa | rojo |
| 2 | platano | amarillo |
+------+---------+----------+</code>

gracias me sirvio de mucho

gracias pedro

Buenísima sugerencia! Gracias

Hola tengo un problemita, miren yo uso SQlite y al ejecutar la peticion

SELECT *,(SELECT COUNT(*) FROM `personajes`) AS `total` FROM (SELECT *, (@rownid := @rownid + 1) AS `rank` \
FROM `personajes`, (SELECT @rownum := 0) AS `X` ORDER BY `Monedas` DESC) AS `Y` WHERE `pj` = 'Sylvert'

me tira esto " unrecognized token: ":" "

como puedo solucionarlo?

Muy buena idea. Funciona!!! Muchas gracias. Un saludo, Javier.

¿Sabrá alguien como hacer lo mismo con un UPDATE, por ejemplo actualizar un campo con el valor del ROWNUM generado de esta forma?

muy buena

es lo que hice para el update

UPDATE fruta, (SELECT @rownum:=0)r SET numfila= (@rownum:=@rownum+1) ;

Gracias, me sirvió.

Eres un crack, campeón!! jejej

Añadir nuevo comentario