SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting questions (MySQL)

    I have a couple of ORDER BY questions that I hope someone can and want to answer.

    1) Is there any easy way I can achieve a natural sorting order with both numbers and letter (i.e. 1, 2, 10, 11, 20, 100, a2, a100, b5 etc.) in MySQL? I've found some suggestions involving ASC 0, but they don't seem to work when letters are involved.

    2) Is there any way to create a sort on SQL level (rather than, e.g., PHP) that puts all rows with a certain value at the end of the list, for example 0 in an INT column that contains years. In the cases where the years is unknown the rows have 0, which I would like to come last in both ascending and descending order. Similarly with empty strings in other column types.

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1) nothing easy... i recommend a second column, declared INTEGER, for sorting

    2) use a CASE statement

    for example, to put 0's last in ascending order...
    Code:
    ORDER
        BY CASE WHEN foo = 0
                THEN 'humpty'
                ELSE 'dumpty' ASC
         , foo ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1) nothing easy... i recommend a second column, declared INTEGER, for sorting
    Great suggestion, as always. Thanks.

    Quote Originally Posted by r937 View Post
    2) use a CASE statement

    for example, to put 0's last in ascending order...
    Code:
    ORDER
        BY CASE WHEN foo = 0
                THEN 'humpty'
                ELSE 'dumpty' ASC
         , foo ASC
    Probably great as well. I'll just have to understand it before deciding.

    Edit: I obviously need to read up a little more on CASE syntax, but is the first ASC a typo for END, or have I done something wrong that makes it work with END but not ASC?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by atoroqo View Post
    ...is the first ASC a typo for END
    aaargh, my bad, yes, i forgot to put END before ASC there

    ASC, of course, because you want dumpty rows before humpty rows

    i know ASC is default, but in these cases it helps to have it there explicitly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2013
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, yes, thanks for the clarification.


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
  •