SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)

    multiple order bys in one query?

    Hi,

    I have a table that has username and users name (as well as other columns).

    I want to sort the columns alphabetically which is very simple; however a wrinkle is that sometimes the users name can be blank while a username is unique and cannot be blank. I don't want to order by username as my name could be Steve but my username could be Ictus, so sorting by users names is what is needed and the blank user names should be put at the end of the alphabetisation. To get this I have done the following:
    Code:
                    SELECT 
                        username, users_name 
                    FROM users 
                    ORDER 
                        BY case when length(users_name) > 0 
                        then 0 
                        else 1 end
                         , users_name";
    This almost works but then the blank user_names are left unsorted at the end of the result set. like:

    | --Name-- | Username |
    |Alfred Alphons | aalphons |
    |Bill Bailey | wizard |
    |----------------| Pictures |
    |--------------- | empty_one |

    (sorry about the crappy formating... don't know how to make this look more tablature)

    You can see that the empty names with the usernames 'Pictures' and 'empty_one' are not alphabetized. Do you know how I could order by to get:

    | --Name-- | Username |
    |Alfred Alphons | aalphons |
    |Bill Bailey | wizard |
    |--------------- | empty_one |
    |----------------| Pictures |

    I tried:
    Code:
    SELECT 
      username,raw_password,users_name 
    FROM users 
             ORDER 
                        BY 
                        CASE 
                            when length(users_name) > 0 
                            then 0
                            else 1 END
                           , 
                           CASE 
                               when length(users_name) > 0
                              then 'users_name'
                              else 'username' END
    but this does not work.

    Any ideas?

    Regards,
    Steve
    ictus==""

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    ORDER 
       BY CASE WHEN LENGTH(users_name) > 0 
          THEN 0
          ELSE 1 END
        , users_name
        , username


    p.s. can the users_name column be NULL as well as blank?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    r937
    Again to the rescue!!!

    Hi thanks for this... much simpler and I tried so many different ways

    I notice if the two blank fields are 'Pictures and Empty One' this order by works perfectly; however if the field names are 'Pictures' and 'empty_one' the order stays 'Pictures' and then 'empty_one'. Does Order By sort by capitalization as well?

    Yes the fields can be NULL; should I set a default value as '' ?


    Regards,
    Steve
    ictus==""

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ServerStorm View Post
    Does Order By sort by capitalization as well?
    it depends

    check out your username values, if there are any mixed case ones, then they should be out of order too

    of course, it could be the NULL issue

    you said "however if the field names are 'Pictures' and 'empty_one' ..." and i'm guessing one of those has a NULL user_names, the other one an empty string

    Quote Originally Posted by ServerStorm View Post
    Yes the fields can be NULL; should I set a default value as '' ?
    no, that's a step in the wrong direction, you shouldn't let any varchar have a default value of an empty string, but that's a different topic for another day

    meanwhile, change the CASE to --
    Code:
    CASE WHEN LENGTH(COALESCE(users_name,'')) > 0
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    Hi r937,

    I checked the values and they both are NULLs.

    I change the CASE as you suggested but no difference 'Pictures' is still ordered before 'empty_ones'.

    I read in a forum that running from a linux platform Order By can do its' sorting by ascending/desending order and by capitalization. Could this be, as this MySQL is installed on a Linux server?

    Regards,
    Steve
    ictus==""

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i'll be honest and say i dunno

    but i do know how to work around the mixed case issue
    Code:
    ORDER 
       BY CASE WHEN LENGTH(COALESCE(users_name,'')) > 0 
          THEN 0
          ELSE 1 END
        , LOWER(users_name)
        , LOWER(username)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    Rudy,

    Thanks for the help on the mix cases... works beautifully

    Regards,
    Steve
    ictus==""

  8. #8
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    2 Thread(s)
    Incidentally, to be clear to others reading this thread, your last suggestion fixes the alphabetization with the Upper and Lower case so if I have users named 'Pictures', 'carl.simpson' and one named 'bill.anson' then the order will be 'bill.anson', 'carl.simpson', 'Pictures'; without the LOWERCASE function that Rudy put in the case statement the 'ORDER BY' order would be 'Pictures', 'bill.anson', and 'carl.simpson';

    Here is the final SQL statement:
    Code:
                    SELECT 
                        username
                        , users_name 
                    FROM users 
                    ORDER 
                        BY 
                        CASE 
                            when length(COALESCE(users_name,'')) > 0 
                            then 0
                            else 1 END
                           , LOWER(users_name)
                           , LOWER(username)
    ictus==""


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
  •