MySQL Row Number

Use the following MySQL query to return a row number for each record in a recordset:

SELECT @row := @row + 1 AS `RowNumber`, SomeTable.*
FROM SomeTable, (SELECT @row := 0) `DerivedTable`;

Explanation

MySQL allows variables to be assigned and selected in the same statement; therefore, @row := @row + 1 increments the value of the @row variable by one and selects the result. Because the default configuration of MySQL does not allow multiple queries, the value of @row is initialized through a sub-select in the FROM portion of the query. It is important to note that MySQL requires a name for each derived table, in this case the derived table was named DerivedTable. SomeTable and SomeTable.* should be replaced with your table and field list respectively.

Alternate Syntax

If MySQL is configured to allow multiple statements per command (this is turned off by default to prevent several types of SQL injection attacks), you can use the following alternate syntax, which does not require a derived table:

SET @i = 0;
SELECT @i := @i + 1 AS RowNumber, SomeTable.*
FROM SomeTable;