1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

ORDER BY: IF Statement/IFNULL

Discussion in 'MySQL' started by imvain2, Apr 14, 2011.

  1. #1
    I have a band/event database that is setup with two fields that I want to order by. However, there is a little oddity with this order by that is causing me issues. I want to order by last name if that field isn't empty, if it is order by first name. I'm having a problem explaining it, so I can show you example data and what I have done so far.

    (Artists typically use both fields, and a band will just use first name)

    table:

    first_name, last_name
    'Bryan, 'Adams'
    'Belafonte, ''
    'Berlin',''

    My Order by:
    ORDER BY LENGTH(IFNULL(last_name, ''))=0 asc, last_name, first_name

    The problem with this code is it shows all bands together, then all of the artists. Each are working from the stand point all bands are sorted alphabetically and all artists are sorted alphabetically, but I would like them combined.

    Thank you for your help.
     
    imvain2, Apr 14, 2011 IP
  2. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Could you not join the Last_Name & First_Name (in that order) and order by that? Thus if there is no last name it will only be the first name

    ORDER BY (Last_Name + First_Name) asc
    Code (markup):
     
    AstarothSolutions, Apr 15, 2011 IP
    imvain2 likes this.
  3. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #3
    plog, Apr 15, 2011 IP
  4. imvain2

    imvain2 Peon

    Messages:
    218
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks for the idea. I ended up using a combination of your concept and another process:

    select if(last_name ='', first_name, concat(last_name,' , ',first_name) as band_name
    order by band_name

    I tried that originally but wasn't coming out exactly how I wanted.
     
    imvain2, Apr 15, 2011 IP