mysql order by multiple columns

I have a post table and a reply table. both tables have a created column which record when it is being created. and the post tabl开发者_Python百科e have a last_reply_created and is updated whenever a reply made to a post with that post id.

first question: in the posts page, I want to display all posts that sorted by whichever last_reply_created or created in post table comes first.

second question: Can you design the post and reply table better regarding this problem?

My workaround is to insert last_reply_created whenever a post is created.

---

Your last_reply_created should be NULL until there is a reply and the creation time for a reply should always be more recent than the created value. This allows you to use coalesce like this:

select *
from your_table
order by coalesce(last_reply_created, created) desc

And presumably every row has a non-NULL created.

I don't see any problem with caching the last_reply_created value in your post table. If you're going to be using it a lot then it doesn't make much sense to keep computing it over and over again.