I had a requirement to display serial numbers in Zend grids. So, in order to fulfill this, I had a worse idea of using a count variable and displaying the same as serial number but, Zend grids will not allow such worst ideas to implement.

So had explored for a solution to generate serial numbers or what else you call as the row numbers using MySQL query.

I had spent hours on exploring but with some guidance of my colleague, I finally had achieved the query that suits my requirement.

Here it is - an example of how you can generate serial numbers or row numbers using MySQL query:

SELECT (@rownum:=@rownum+1) as SlNo[, <other_fields>] from (SELECT @rownum:=0) alias[, <other_tables>];

Explain?

SELECT @rownum:=0 - this will create a variable “rownum” initialized to zero.

@rownum:=@rownum+1 - this SQL expression will increment the value of rownum just as similar to a count variable in for loop.

Hope this could be useful. Thanks!