Filip  Busic Filip Busic - 3 years ago 85
SQL Question

Complexed Query - Sort by newest with average of 2 other values

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

Desired Result:

Row 1 --> Picture #2
Row 2 --> Picture #1

I greatly appreciate any answers.

Answer Source

The order by clause can be use with more than one column.

Try this; 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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download