Wednesday, September 5, 2012

Natural sorting in MYSQL or Alphanumeric sorting in mysql

MYSQL is still working on Natural sorting.
Here is some quick solution for natural sorting in MYSQL.
This worked perfectly for me.

SELECT name FROM taxonomy_term_data WHERE ORDER BY name+0<>0 DESC, name+0, name

If you have values in name column like below
   2-test
   Test
   12-test
   22-test
   Ascend

& the Output will be:
   2-test
   12-test
   22-test
   Ascend
   Test

Posted by at

5 comments:

  1. AnonymousOctober 12, 2012 at 6:27 PM

    Can you help me?

    my query is SELECT num FROM sortnum ORDER BY lpad(num, 10, 0)

    result of this query is not good here is the result 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1a 2a 2b A1 A2 A3 A4 B1 A10 A11 B10

    What is the best query for this type of data i want number first, then data starting with a , then with b

    i want result like

    1 1a 2 2a 2b 3 4 5 6 7 8 9 10 11 12 13 14 A1 A2 A3 A4 A10 A11 B1 B10

    Thanks in advance

    ReplyDelete
  2. AnonymousDecember 11, 2012 at 1:49 PM

    Thanks for the tip! Worked nicely!

    ReplyDelete
  3. bibinDecember 20, 2012 at 2:03 PM

    nice one thankyou... and try to seo your site by this http://www.spixup.org/2012/12/display-authour-picture-google-search.html

    ReplyDelete
  4. AnonymousJanuary 6, 2014 at 1:02 PM

    After reading your instructive tutorial I became able to create a beautiful looking slideshow. Thanks a lot for proper description, it helps me a lot drupal

    ReplyDelete
  5. AnonymousSeptember 14, 2014 at 4:16 PM

    This query doesn't sort naturally if numbers follow letters in a list of alphanumeric value. Here is an example of what you get:

    abc1
    abc10
    abc11
    abc2

    ReplyDelete

Subscribe to: Post Comments (Atom)