I'm trying to sort by date and average likes/dislikes.
Picture #1: 5 likes, 2 dislikes, created 1 week ago
Picture #2: 5 likes, 2 dislikes, created a minute ago
Example: If picture #1 has 5 likes and 2 dislikes but is created 1 week ago, then this wouldn't show up on the first row because picture #2 has the same amount of likes and dislikes but is newer than picture #1.
This is what I have so far:
SELECT * FROM `pictures` GROUP BY id ORDER BY avg(likes/dislikes) desc LIMIT 10
Row 1 --> Picture #2
Row 2 --> Picture #1
order by clause can be use with more than one column.
ORDER BY avg(likes/dislikes) desc, created desc (assuming
created is the name of your date column)
It first orders the result set by the average of likes/dislikes , then it orders lines with sames avg, by created dates.
@update Sqlfiddle example
@update2 Case expression can be use in the order by clause, see an example: Sqlfiddle example with case in order by