SQL Server - Row ID in MySql 5.0 - Asked By Rajesh Madhukar on 24-Jan-07 04:49 AM

Hi,

    I'm using cursor in my Mysql 5.0 code running over a temporary table. I want to update some fields of the table of the current row. How can I get reference of the current row, assuming that  table could contain duplicate rows? How can I get the row number of a row in MySql?

Rajesh Madhukar

There is no rowid in MySQL. If you need a 'rowid' that you can reference from the table, you should be using the quite common 'auto_increment' type.

For example:
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.
ex.,
SELECT * FROM TABLE1 WHERE _rowid=1

just refer to http://dev.mysql.com/doc/refman/5.0/en/index.html for more info!

end of post

Here is the query for you.

You can dynamically add the rownum to the table if it does not contain any numeric field as primary key for that table.

SET @rownum :=0;
select @rownum := @rownum + 1 as 'ID', EmployeeName FROM Employees

Hope this helps.

Great answer!..
Its working fine for me....
Thanks a lot