mysql order by multiple columns [duplicate]

Question

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 table 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.

mu is too short · Accepted Answer · 2011-05-09 09:18:14Z

1

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.

answered May 9, 2011 at 9:18

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

David Fells Over a year ago

Thanks for that tip, I never noticed that change sir! Killed my answer as yours is sufficient :)

mu is too short Over a year ago

@David: I didn't mean to rain on your parade but you always have to keep an eye on those NULLs.

Collectives™ on Stack Overflow

mysql order by multiple columns [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related