Multiple order bys in one query?

Databases
1

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:


                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:


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

2 
ORDER 
   BY CASE WHEN LENGTH(users_name) > 0 
      THEN 0
      ELSE 1 END
    , users_name
    , username

:slight_smile:

p.s. can the users_name column be NULL as well as blank?

3

r937
Again to the rescue!!!

Hi thanks for this… much simpler and I tried so many different ways :slight_smile:

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

4

it depends :wink:

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

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 –


CASE WHEN LENGTH(COALESCE(users_name,'')) > 0
5

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

6

i’ll be honest and say i dunno

but i do know how to work around the mixed case issue

ORDER 
   BY CASE WHEN LENGTH(COALESCE(users_name,'')) > 0 
      THEN 0
      ELSE 1 END
    , LOWER(users_name)
    , LOWER(username)
7

Rudy,

Thanks for the help on the mix cases… works beautifully :slight_smile:

Regards,
Steve

8

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:


                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)