MySQL Forums
Forum List  »  Newbie

ORDER BY COUNT(message_time) >= NOW() DESC - not the right results
Posted by: VJ Duster
Date: April 27, 2008 12:33PM

Here is a sample set of data (ignore the dashes - - - - - - between columns):

topic_id - - - message_time

1 - - - - - - 2008-04-12 16:27:56
1 - - - - - - 2008-04-12 16:27:56
1 - - - - - - 2008-04-12 16:27:56
2 - - - - - - 2008-04-13 16:27:56
1 - - - - - - 2008-04-26 17:27:56
2 - - - - - - 2008-04-26 18:27:56
2 - - - - - - 2008-04-26 19:27:56
2 - - - - - - 2008-04-26 20:27:56

What I would like have happen is for the topics that have the most entries within the last seven days to be returned first. If there is a tie, it should go to the one with the most recent entry.

Below is the query I started with, but it doesn't seem to even be getting the first part of what I want. Any help would be greatly appreciated.

SELECT DISTINCT(topic_id) AS topic_id
FROM messages
ORDER BY COUNT(message_time) >= NOW()
LIMIT 0,25;

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.