Howard Ha
  • Blog
  • About
  • Business Opportunities
  • Contact

SELECT row number from MySQL While Sorting With Multiple ORDER BY Columns

10/29/2010

1 Comment

 
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
CFNM Swim Team link
7/20/2012 01:37:43 am

Was just bored and thought I would post to say hello

Reply



Leave a Reply.

    About Me

    I'm a web entrepreneur.

    I have a passion for technology, web scalability, gaming, life, reading, and many other things.

    I believe in good, trust, enjoying the little things, and the pursuit of perfection.

    Archives

    December 2010
    October 2010
    August 2010
    July 2010

    Categories

    All
    Captcha
    Ipb
    Mysql
    Rss
    Wordpress



    Follow Me:
    • Facebook
    • Twitter
    • darkcobalt (Neoseeker related tweets)
    • RSS Feeds
    Click to set custom HTML
    www.flickr.com
    howardhaGo to howardha's photostream
Powered by Create your own unique website with customizable templates.