Order rows in mysql query by mixed columns

Question

Hi! I have a content site in wich each content item has create date and number of views. I want to display content in such order: mixed date and views, that in details each view must have some weight and date parameter also has weight, so some content item with max sum of "weights" must be first. Is there standart solution for my problem?

Answer 1

Presumably you're wanting to weight the age (in minutes, maybe) of your post. Assuming you have a column called TIMESTAMP, you can get the age in minutes with this expression.

TIMESTAMPDIFF(MINUTE,`TIMESTAMP`,NOW())

So what makes a post interesting? This is your weighting scheme. Let's say number of views (in column VIEWS) minus the age in minutes, for simplicity.

So, do this

ORDER BY (`VIEWS` - TIMESTAMPDIFF(MINUTE,`TIMESTAMP`,NOW())) DESC

and you should get started. You're going to have to mess around with the formula to make it make sense, though.