How to show sequential number in MySQL query result
The order gets scrambled if you are using GROUP BY
clause in your query. The work around is putting your query inside the FROM
clause like this.
SET @a:=0;
SELECT @a:=@a+1 No, output.*
FROM (
SELECT foo, bar
FROM table GROUP BY foo, bar
) output;
select @rownum:=@rownum+1 No, foo, bar from table, (SELECT @rownum:=0) r;
Neither of the answers worked for me, but based on bungdito's source, I realized you can do something easier:
SET @a:=0;
SELECT @a:=@a+1 No, foo, bar
FROM table;
So, first make sure SET @a:=0;
runs. That will set up your variable, a. Then you can increment it in the results with @a:=@a+1
. I tested this with GROUP BY, ORDER BY, even JOINS and it worked as expected.