SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: sorting issue

  1. #1
    SitePoint Zealot xPox's Avatar
    Join Date
    Sep 2005
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sorting issue

    I'm running a query on a table that contains a column of type varchar. Here are some example row values for this column:

    001-0324
    001-0125
    001-0085
    001-1294
    001-1035

    When I run a query that sorts by this column, I get them in this order:

    001-0085
    001-1035
    001-0125
    001-1294
    001-0324

    What I need is for them to be sorted this way:

    001-0085
    001-0125
    001-0324
    001-1035
    001-1294

    Anyone know why it sorts in this way? Should I be using something other than varchar?

  2. #2
    SitePoint Zealot Baron Ragnarok's Avatar
    Join Date
    Nov 2005
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok....... How do you make MySQL order that column like that, can you type the command for us?

    B.R.

  3. #3
    SitePoint Zealot xPox's Avatar
    Join Date
    Sep 2005
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM members ORDER BY id_number ASC

    It seems to be ignoring the leading zeros when it sorts.

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,189
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)

    leading zeroes

    You may have to give the field the BINARY attribute to sort it correctly.
    All string comparisons are case insensitive by default, with sort ordering determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done according to the ASCII order used on the MySQL server host.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •