MySQL - order by two columns with condition

Question

I have this data in MySQL table. I need order results by condition:

• if effective_to is NULL, to show first and order by effective_from DESC
• if effective_to is NOT NULL, order by effective_to DESC

my data

name            effective_from      effective_to
person 01       1999-04-01          1999-05-31
person 02       1999-04-01          2000-07-06
person 03       1999-04-01          2000-09-25
person 04       1999-04-01          2000-09-25
person 07       1999-04-01          2000-09-25
person 05       2013-04-29          NULL
person 08       2010-06-17          2012-09-27
person 09       2010-12-02          2012-09-27
person 10       2017-02-10          NULL
person 11       2017-02-10          NULL
person 12       1999-04-01          2000-07-06
person 13       2011-04-28          2015-10-06
person 05       2013-04-29          2017-02-15
person 06       2015-09-22          2017-02-15
person 06       2015-09-22          2017-02-10

I need this results order

name                effective_from  effective_to                                                
person 11       2017-02-10          NULL
person 10       2017-02-10          NULL
person 05       2013-04-29          NULL
person 06       2015-09-22          2017-02-15
person 05       2013-04-29          2017-02-15
person 06       2015-09-22          2017-02-10
person 13       2011-04-28          2015-10-06
person 09       2010-12-02          2012-09-27
person 08       2010-06-17          2012-09-27
person 07       1999-04-01          2000-09-25
person 03       1999-04-01          2000-09-25
person 04       1999-04-01          2000-09-25
person 12       1999-04-01          2000-07-06
person 02       1999-04-01          2000-07-06
person 01       1999-04-01          1999-05-31

How I do this?

Answer 1

(Posted on behalf of the OP).

I resolve my problem like this:

ORDER BY `effective_to` IS NOT NULL ASC, `effective_to` DESC,
    `effective_to` IS NULL, `effective_from` DESC;

Answer 2

This should do it

...
ORDER BY
ISNULL(effective_from) DESC,
IF(ISNULL(effective_to ) = 1, effective_from  , effective_to) DESC