MySQL: Auto increment temporary column in select statement
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 |
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
Try this:
SET @rownr=0;
SELECT @rownr:=@rownr+1 AS rowNumber, rowID
FROM myTable
WHERE CategoryID = 1
In modern versions of MySQL you can use MySQL Window: http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/
Example:
SELECT ROW_NUMBER() OVER ( ORDER BY productName ) row_num,
productName, msrp
FROM products ORDER BY productName;