Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts

    get results in alphanumric order, but letters first then numbers and other char-

    How would I get all mysql results returned to be in order by letter then numbers after and then the other characters.

    i.e.

    a
    b
    c
    d
    ...
    0 (zero)
    1
    2
    3
    4


    the following code i was given shows the results like so...
    Code:
    SELECT * FROM table_name ORDER BY LEFT(col_name,1) > 0, left(col_name,1), substring(col_name,2) > 0
    0 (zero)
    a
    b
    c
    d
    ...
    1
    2
    3
    4

    and the zero is not in the right order...

    how do i get this to work right ?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Don't ask for explanation why it works, was just playing around with it.

    Code:
    select f, Concat(f,1)*1
    from  table_name
    order by Concat(f,1)*1,f
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,426 Times in 4,391 Posts
    That doesn't work, at all, Bubikol!

    Code:
    mysql> select f, concat(f,1)*1 from f order by concat(f,1)*1,f;
    +------+---------------+
    | f    | concat(f,1)*1 |
    +------+---------------+
    | 0a   |             0 |
    | a0   |             0 |
    | abcd |             0 |
    | 02x  |             2 |
    | 3A   |             3 |
    | 012  |           121 |
    +------+---------------+
    6 rows in set (0.00 sec)
    The reason it *seems* to work: If you multiply a non-number by 1, MySQL converts the non-number to zero before the multiply. So that means that any data that starts with anything except a digit is treated the same as any data starting with a zero.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,426 Times in 4,391 Posts
    needssomehelp: We *CAN* play with the LEFT(field,1) to get the data in the right order so far as the FIRST CHARACTER is concerned.

    Example:
    Code:
    mysql> select f from f order by LEFT(f,1) < 'A', left(f,1), substring(f,2);
    +------+
    | f    |
    +------+
    | a0   |
    | abcd |
    | 012  |
    | 02x  |
    | 0a   |
    | 3A   |
    +------+
    6 rows in set (0.00 sec)
    But as you can see, then the SECOND character ordering is wrong.

    To do this for an arbitrary number of characters would take an ORDER BY expression that would be a mile long and still would fail if you added one more character to a the field that you hadn't accounted for.

    If you need this ordering (letters before digits) to be effective for all characters in the field, you will have to create a custom collation order.

    Look here, for starters:
    http://download.oracle.com/docs/cd/E...collation.html
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Just thinking about it what about if all entries were got in ascii order

    SELECT * FROM `products` ORDER BY `productTitle` ASC

    then have the entries that start with a letter extracted out and placed in a new array then have the entries that start with a number added to the end of the new array, as the query has already done an 'order by' only the extract and paste in to a new array would be needed?

    EDIT: but still use the array string as a normal mysql results array string for the rest of page to process.

    is this a better idea ?
    Last edited by needsomehelp; 10-14-2011 at 08:36 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,426 Times in 4,391 Posts
    have the entries that start with a letter extracted out and placed in a new array then have the entries that start with a number added to the end of the new array
    And how does that solve the problem of getting the *SECOND* character in the field in your non-ASCII order???

    How does that help get (for the simplest example)
    Code:
    aa
    a0
    0a
    00
    into that order?
    Yes, that would put the "a" ahead of the "0" for the first character, but then the second characters would give you the natural ordering to produce
    Code:
    a0
    aa
    00
    0a
    If that's adequate, then it's trivial. Without your complex scheme.

    Code:
    mysql> select f from f order by LEFT(f,1) < 'A', f;
    +------+
    | f    |
    +------+
    | a0   |
    | abcd |
    | 012  |
    | 02x  |
    | 0a   |
    | 3A   |
    +------+
    6 rows in set (0.00 sec)
    But if you want *ALL* characters to be sorted in the order you asked for, there is NO good solution other than creating a custom collation order.

    Did you look at the docs I pointed you to?

    Did you look at this section:
    http://download.oracle.com/docs/cd/E...mple-8bit.html

    That's really a simple process, and it will give much better performance and solve the problem of making sure that that ordering is correct for *ALL* characters in the field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Old Pedant View Post
    Code:
    mysql> select f from f order by LEFT(f,1) < 'A', f;
    +------+
    | f    |
    +------+
    | a0   |
    | abcd |
    | 012  |
    | 02x  |
    | 0a   |
    | 3A   |
    +------+
    6 rows in set (0.00 sec)
    This done the trick, ok not perfect but it got the index to show in the right order, part from the numbers coming before in each of these list this I can live with.

    Thank you for your help on this.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,426 Times in 4,391 Posts
    LOL! Teach me to try for perfection. <grin/> If that's adequate, go for it!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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