Contact me sending an e-mail (antispam defense activated)
Title: Oracle rownum in MySQL
Author: Sandro Tosi
Last modified: 2006-05-24
Oracle database offers the pseudo-column ``rownum'' (it's a
pseudo-column since doesn't exist in table definition): it is
evaluated during row retrieval (thus it's assigned only to rows
passing the WHERE clause test) and returns, for each row, a number
that indicates the order in which the row was selected from the table;
I mean, if you execute a select and it return 100 rows, the first one
has rownum = 1, the second rownum = 2, and so on until the last row,
that has rownum = 100.
Rownum allows you to select only the first x rows ( adding the
where-condition: (and) rownum <= x ) but it gives a nice number for
each row, useful if you need to create a list (numbered) from returned
rows.
MySQL, sadly, has nothing like rownum built-in, but provides tools to
act like rownum.
First situation (retrieve only first x rows) is translated into MySQL
with LIMIT construct:
mysql> select * from tab LIMIT x;
will output only first x rows. You can choose to start from a row x
and output the next y rows with:
mysql> select * from tab LIMIT x OFFSET y;
that return the rows x+1..x+y . You can even decide to select rows
from the x-th and y-th:
mysql> select * from tab LIMIT x,y;
There are many articles around about LIMIT, for example:
http://www.petefreitag.com/item/451.cfm
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dotnet.org.za/thea/archive/2005/02/22/14715.aspx
http://lists.evolt.org/archive/Week-of-Mon-20000800/015189.html
http://forums.mysql.com/read.php?61,402,402#msg-402
But, what if I really need a sequential number for extracted rows? In
MySQL you can solve this with variables:
mysql> SET @rownum:=0; SELECT @rownum:=@rownum+1 AS rownum, * FROM tab;
and this will generate something like this:
mysql> SET @rownum := 0; SELECT @rownum := @rownum+1 AS rownum, gara
from gare group by gara;
+--------+-------+
| rownum | gara |
+--------+-------+
| 1 | 100DO |
| 2 | 100FA |
| 3 | 100MX |
| 4 | 100RA |
| 5 | 100SL |
| 6 | 2000m |
| 7 | 200DO |
| 8 | 200MX |
| 9 | 200RA |
| 10 | 200SL |
| 11 | 3000m |
| 12 | 400SL |
| 13 | 50DO |
| 14 | 50FA |
| 15 | 50RA |
| 16 | 50SL |
+--------+-------+
(code from http://www.joegrossberg.com/archives/000038.html).
In http://dev.mysql.com/doc/refman/5.0/en/user-variables.html , we can
find a nice snippet of code:
SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;
that can be used on MySQL >= 5.0, to avoid variable set outside the
select statement.