MySQL: Auto increment temporary column in select statement

Question

How do I create and auto increment a temporary column in my select statement with MySQL?

Here is what I have so far:

SET @cnt = 0; SELECT     (@cnt =@cnt + 1) AS rowNumber,     rowID FROM myTable WHERE CategoryID = 1 

Which returns:

+++++++++++++++++++++ + rowNumber | rowID + +++++++++++++++++++++ +  (NULL)   |   1   + +  (NULL)   |   25  + +  (NULL)   |   33  + +  (NULL)   |   150 + +  (NULL)   |   219 + +++++++++++++++++++++ 

But I need:

+++++++++++++++++++++ + rowNumber | rowID + +++++++++++++++++++++ +  1        |   1   + +  2        |   25  + +  3        |   33  + +  4        |   150 + +  ...      |   ... + +++++++++++++++++++++ 

Answer 1

This will give you a consecutive row number with 3.

SELECT     (@cnt := @cnt + 1) AS rowNumber,     t.rowID FROM myTable AS t   CROSS JOIN (SELECT @cnt := 0) AS dummy WHERE t.CategoryID = 1 ORDER BY t.rowID ; 

Result

| ROWNUMBER | ROWID | --------------------- |         1 |     1 | |         2 |    25 | |         3 |    33 | |         4 |   150 |

Answer 2

But what if you have a group by in the select statement? the counting will be off.

For such cases, the only solution I found is nesting select:

SELECT (@cnt := @cnt + 1) AS rowNumber, t.* from (select     t.rowID FROM myTable  WHERE CategoryID = 1 ORDER BY rowID) t CROSS JOIN (SELECT @cnt := 0) AS dummy