How to show sequential number in MySQL query result

I have some simple query:

SELECT foo, bar开发者_运维问答 FROM table

i think you now whats the result looks like.

What I want to do is to show some sequential number based on how many data appear from query result. its just like AUTO_INCREMENT(its not mean i want to show ID). The result what I want is like:

|No|   foo   |    bar   |
-------------------------
|1 |   bla   |    123   |
|2 |   boo   |    abc   |
|3 |   wow   |    xxx   |

How should I do to make it?

thanks in advance

select @rownum:=@rownum+1 No, foo, bar from table, (SELECT @rownum:=0) r;

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;

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.

If somebody wants to display the row number after ordering records, something like this may work

set @a=0; 
select @a:=@a+1 serial_number,t.* 
from ( SELECT foo, bar FROM tableORDER BY bar ) t