Create new sequential number for MySQL query result.

It’s kind of simple request from client that they want to numbering query result like

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

The original query like

SELECT foo, bar FROM table WHERE cond LIMIT 3

So we need create sequential number in this query. The new query looks like:

SELECT @rownum:=@rownum+1 AS No, foo, bar FROM table JOIN (SELECT @rownum:=0) AS n 

You can see we JOIN a new table, which provide @rownum an initial value. And this solved the issue and the result shows as expected.


About qianggan

Sr. Software Engineer
This entry was posted in Computers and Internet, 计算机与 Internet. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s