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.

Tags:

Mysql

Select