按两个字段不同情况排序

表如下：
t_article(uid, title, inputdate, chgdate)
其中inputdate, chgdate都是datetime类型的，chgdate可能为空
现在要实现如下功能：
取出t_article的所有记录，如果chgdate不为空则以chgdate的DESC排序，如果chgdate为空则以inputdate的DESC排序。
也就是结果记录先以chgdate逆序排序，后已inputdate逆序排序。chgdate不为空的情况下优先级比较高。

 

create table t_article(uid varchar(10), title varchar(20), inputdate datetime, chgdate datetime)

insert into t_article select '0001','Hello','2007-11-01',NULL
insert into t_article select '0002','test',NULL,'2008-01-05'
insert into t_article select '0003','dsfasfd','2007-11-01',NULL
insert into t_article select '0004','efef','2005-10-02','2008-10-02'
insert into t_article select '0005','hgh','2006-10-01',NULL
insert into t_article select '0006','tjht',NULL,'2007-05-08'
insert into t_article select '0007','bffd','2007-07-06','2004-02-06'


select * from t_article
order by case
           when inputdate is not null and chgdate is not null then inputdate
           when inputdate is not null then inputdate end desc,
          case
           when chgdate is not null and inputdate is null then chgdate end desc


/*
uid    title   inputdate        chgdate
0001    Hello    2007-11-01 00:00:00.000    NULL
0003    dsfasfd    2007-11-01 00:00:00.000    NULL
0007    bffd    2007-07-06 00:00:00.000    2004-02-06 00:00:00.000
0005    hgh    2006-10-01 00:00:00.000    NULL
0004    efef    2005-10-02 00:00:00.000    2008-10-02 00:00:00.000
0002    test    NULL            2008-01-05 00:00:00.000
0006    tjht    NULL            2007-05-08 00:00:00.000

*/