Use different column in order by clause if value is NULL

dajense · #1 (**permalink**) 06-30-10, 07:34

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.

shammat · #2 (**permalink**) 06-30-10, 07:57

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;

dajense · #3 (**permalink**) 06-30-10, 08:18

Thanks a lot. That's pretty much exactly what I need.

gvee · #4 (**permalink**) 06-30-10, 08:27

Personally, in this scenario I'd use the Coalesce() function

Code:

ORDER
    BY Coalesce(publ_date, creat_date)

Internet Services

Web Development

Digital Marketing

Consumer Tech