14 January 2008

MySQL: Sort ascending with blanks at end

It makes sense to have blanks at the start of an A-Z query, as a shorter length string should always appear before a longer length string starting with the same letters. However, sometimes it's convenient to clump any completely black records at the end.

I couldn't think of a perfect way of doing this, but here's a fairly nice solution...

ORDER BY IF(LENGTH(name)>0, CONCAT('1',name), '2') ASC

The IF() function is used to prepend a '1' to any non-zero length string, and '2' to any zero length string. For the latter, we already know that the string is empty so there's no need to use CONCAT().

at

3 comments:

  1. Anonymous20 May 2013 at 20:43

    I have been surfing online greater than three hours lately, but I never discovered any fascinating article like yours.
    It is lovely worth enough for me. In my opinion, if all
    site owners and bloggers made excellent content material as you probably did, the web might be a lot more useful than ever before.


    My site Louis Vuitton Handbags

    ReplyDelete
  2. Anonymous26 May 2013 at 11:30

    You've made some decent points there. I looked on the internet for more info about the issue and found most individuals will go along with your views on this website.

    Also visit my blog post - Abercrombie

    ReplyDelete
  3. Anonymous2 June 2013 at 01:06

    Heya i'm for the first time here. I found this board and I find It truly useful & it helped me out a lot. I hope to give something back and aid others like you aided me.

    My site - Go Here

    ReplyDelete