mySQL row counter

Every now and then I come across a snippet of code which is incredibly useful at the time, but rarely used. The problem with these snippets is that I usually forget the particulars by the next time I need to use it.

Today I needed to use such a snippet and luckily for me, I knew exactly where I had last used it and was able to go straight to the source and grab it. This piece of SQL adds a row counter column to the result set. This is handy if you need to add a rank to records on a bulk insert, or create a new sequential key for on-the-fly table joins.

Here's a fairly simple example where the result set would return up to 50 rows:

SELECT (@rownum:=@rownum+1) rank, fieldname
FROM (SELECT @rownum:=0) ranks, tablename
WHERE something=TRUE ORDER BY fieldname ASC LIMIT 50

This concept can be taken one step further. In this example we update table_one's new_id field with table_two.id sequentially from the row with id value of 151.

UPDATE table_one INNER JOIN (Select @rownum:=@rownum+1 rank, id
FROM (SELECT @rownum:=150) ranks, table_two
WHERE something=TRUE) temp_table ON table_one.id = temp_table.rank
SET table_one.new_id = temp_table.id

Obviously this second example will only work where the id values are entirely sequential (no missing rows), but it worked well for the task I had to do and saved me from dumping the data into a temporary table just to be able to join for a one-to-one update.

I hope you'll find this as useful and interesting as I did :)