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 
WHERE cond LIMIT 3;

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.

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s