MySQL order by multiple fields

Question

In MySQL, I have this table:

id   updateTime  writeTime 
------------------------------
1     null       2017-04-05 
2     null       2017-04-01 
3    2017-04-02  2017-04-02
4    2017-04-02  2017-03-31

I want this result:

id   updateTime  writeTime 
------------------------------
1     null       2017-04-05 
3    2017-04-02  2017-04-02
4    2017-04-02  2017-03-31
2     null       2017-04-01  `

But when I use ORDER BY updateTime, writeTime DESC, I usually get:

id   updateTime  writeTime 
------------------------------
3    2017-04-02  2017-04-02
4    2017-04-02  2017-03-31
1     null       2017-04-05 
2     null       2017-04-01  `

I would copy the value of writeTime into updateTime for rows that have not been updated. This way you can order by updateTime using an index. — Paul Spiegel
– Paul Spiegel, Commented Apr 3, 2017 at 16:21

Gurwinder Singh · Accepted Answer · 2017-04-03 15:50:59Z

1

If I understand it right, you want to sort by updateTime. If that's null, sort by Writetime. In case of equal updateTime, sort by writeTime.

You can use coalesce for this:

select *
from your_table
order by coalesce(updateTime, writeTime) desc, writeTime desc;

answered Apr 3, 2017 at 15:50

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charif DZ · Accepted Answer · 2017-04-03 16:03:14Z

0

I think you want to order by the greatest date :

select 
   *
from your_table
order by coalesce(GREATEST(updateTime, writeTime),updateTime, writeTime) desc;

answered Apr 3, 2017 at 16:03

Charif DZ

14.8k3 gold badges25 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL order by multiple fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related