CODEWITHSUNDEEP
phpmysqlsql-server
Ryan
Ryan

Reputation: 1164

Complex SQL Query - Popular Content

So, I'm writing a query to select data from the database, and display it in an order of popularity. I understand that this is simple enough to do if it were just ordering it through something such as ORDER BY numLikes DESC or something, but in this case, I am wanting to order the content by numLikes and through datePosted.

How would I sort the content in a way that it displays the top content from today's date? If it sorted purely through numLikes, The top content would float to the top and stay there. I want this content to be sorted through a daily basis. This is to allow for a system where the user can choose popular posts from the past day, past week, past month, and all time top posts.

Could this be done through a single SQL query? Would an SQL multi-query have to be done? Is SQL powerful enough to do this, or is PHP required to play a role?

Thanks!

Upvotes: 0

Views: 50

Answers (1)

dotNET
dotNET

Reputation: 35400

You just need to sort on datePosted first and then by numLikes. Something on the following lines:

SELECT * FROM tableName ORDER BY datePosted, numLikes DESC

If you want top post between a particular range, you can do:

SELECT TOP 1 * FROM tableName 
WHERE datePosted BETWEEN [MinDate] AND [MaxDate]
ORDER BY datePosted, numLikes DESC

Replace [MinDate] and [MaxDate] with the dates you need, e.g. for the past week, they would be something like April-14 and April-20.

Upvotes: 2

Related Questions