Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    17

    Unanswered: Use different column in order by clause if value is NULL

    Hi,

    I need some advice.

    Let's say I have a table article(id, publ_date, creat_date) and I want to order this table by publ_date. Problem, sometimes publish date may be NULL, so only if the publ_date for a row is NULL I want MySQL to use the creat_date for the order by clause.

    So I need a way to substitute the attribute value within the statement, only if the attribute is NULL, and then only for this specific row. I was thinking of creating a virtual column or using control flow functions, but both are new to me, so if anybody has some experience if this is possible using either of the two or any other ideas how to solve this, I would really appreciate your advice.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Something along the lines
    Code:
    SELECT <your column list>
    FROM article
    ORDER BY 
       CASE 
          WHEN publ_date IS NULL THEN creat_date
          ELSE publ_date
       END;

  3. #3
    Join Date
    Jul 2009
    Posts
    17
    Thanks a lot. That's pretty much exactly what I need.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally, in this scenario I'd use the Coalesce() function
    Code:
    ORDER
        BY Coalesce(publ_date, creat_date)
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •