user2896120 user2896120 - 2 months ago 8
MySQL Question

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.

Answer

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;