Reputation: 3282
Ordering by two columns not working
I am trying to create a "Trending" list where the top 10 articles show up. The trend list is based on most recent and most views. This is what my current SQL query looks like:
$resultSet = $db->query("SELECT * FROM Articles ORDER BY Counter DESC, dateStamp LIMIT 10");
Counter is the column that stores how many views each article has. dateStamp is the datetime column that stores the datetime of each article. My current SQL works for the number of views. It orders the article that has the most views on top and the article that has least views on the bottom.
The only problem that I am experiencing with this current SQL is that it does not take the most recent dates only. It looks for all the dates and only orders the query based on views. An article could be 2 years long, but it'll have more views so it'll be in the trending list.
How can I make it so that the query orders the results based on both recent and most views?
Example Data:
Date: 8/1/15 Views: 20,000
Date: 9/30/16 Views: 500
Date: 9/28/16 Views: 400
Date: 9/25/16 Views: 150
Date: 9/22/16 Views: 100
Date: 9/21/16 Views: 98
Date: 9/20/16 Views: 92
Date: 9/18/16 Views: 10
Date: 9/15/16 Views: 5
Date: 9/12/16 Views: 3
Technically, the data should look like this:
Date: 9/30/16 Views: 500
Date: 9/28/16 Views: 400
Date: 9/25/16 Views: 150
Date: 9/22/16 Views: 100
Date: 9/21/16 Views: 98
Date: 9/20/16 Views: 92
Date: 9/18/16 Views: 10
Date: 9/15/16 Views: 5
Date: 9/12/16 Views: 3
Date: 9/11/16 Views: 2
It's not organizing the most recent dates. I need it to organize the most recent dates and the most views. The views part is working correctly, it's just the dates that's not.
Upvotes: 0
Views: 2163
Answers (2)
Reputation: 614
I think you need to use a where to limit the dates that show up. The order by orders the results based on the first column. It only uses the second column if two rows have the same value for the first column.
Upvotes: 0
Reputation: 94904
This is how to get the 10 most recent records:
SELECT * FROM Articles ORDER BY dateStamp DESC LIMIT 10;
This is how to order by views descending:
SELECT ... ORDER BY Counter DESC;
The two combined:
SELECT *
FROM (SELECT * FROM Articles ORDER BY dateStamp DESC LIMIT 10) TOP10
ORDER BY Counter DESC;
Upvotes: 2