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: Conventional method: 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+.
1 Comment
Leave a Reply. |
About Me
I'm a web entrepreneur based out of Vancouver, BC, Canada. Archives
February 2012
Categories
All
My Sites: Other pages: |
Howard Ha