How MySQL implements Oracle's ROWNUM
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)
"One SQL statement completed, no set variable"
SELECT @rownum: [email protected]+1 as rownum, frutas.*
From (SELECT @rownum: =0) R, Frutas;
mysql> SELECT @rownum: [email protected]+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)
But how to put this query in CREATE view
Mysql> CREATE VIEW Vw_frutas as SELECT @rownum: [email protected]+1 as rownum, frutas.*
-From (SELECT @rownum: =0) R, Frutas;
ERROR 1351 (HY000): View ' s SELECT contains a variable or parameter