Getting the current row number from MySQL

This morning I found a pretty slick way to pull the row number of a query (like Coldfusion’s #currentrow#) directly from MySQL.

select lookupID, lookupValue, lookupType, @rownum:=@rownum+1 as rownumber
from lookup, (SELECT @rownum:=0) r

This will return the lookupID,  lookupValue,  lookupType, and a number for each row, beginning with 1.

All the credit goes to this blog post, but I feel it’s always good to propagate tips like this.