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:
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:
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.