Today I came across an interesting MySQL problem. I needed to SELECT data while also returning the row number of each row of data. Normally to do this you do the following:
SELECT @row := @row + 1 as row, t.*FROM some_table t, (SELECT @row := 0) r
But in my situation I needed to ORDER BY the data using multiple columns, and when you do this the row number gets completely broken (the order goes completely crazy). So after searching and finding nothing I played with the query and came up with the below solution, which is to basically sort the results using as many ORDER BY criteria as you want, then SELECT it again and sort that using the row numbers:
Method to select when you ORDER BY multiple columns.
SELECT @row := @row + 1 as row, t.* FROM (SELECT t.*FROM some_table t ORDER BY t.col1, t.col2) as sorttbl, (SELECT @row := 0) r ORDER BY row
The above works in MySQL 5.1.x and should work in 4.1+.
I'm a web entrepreneur based out of Vancouver, BC, Canada.