Sort SQL Query by most recent question answered (like a forum)
If you're familiar with a forum, when a questio开发者_运维百科n is answered it moves to the top of the list. How does this SQL query work? Right now, my query is as follows:
SELECT *,
(SELECT count(*) FROM tblQA Q1 WHERE Q1.intResponseID = Q2.intQAID) AS answercount,
(SELECT cUsername FROM tblUsers tblU WHERE Q2.intPosterID = tblU.intUserID) AS username,
(SELECT CASE WHEN DAY(dSortDateTime) = DAY(NOW()) THEN DATE_FORMAT(dSortDateTime, 'Today at %l:%i%p')
ELSE DATE_FORMAT(dSortDateTime, '%b %e, %l:%i%p') END) AS post_time
FROM tblQA Q2 WHERE cCategory IN ('Football','Baseball','Basketball','Hockey')
ORDER BY dSortDateTime DESC, dSortDateTime DESC LIMIT 40
So, when a user responds to a question, I'd like that to bounce to the top of the question list. Right now, the way the query is running, its not doing this.
Look forward to your help.
ADDITIONAL INFO: dSortdatetime = date('Y-m-d H:i:s')
I don't know your schema, so I cannot give you the actual SQL, but you would need to store the time that the question was answered (or do a JOIN between the question and answer tables to get the most recent answer for each question). You then order by answer time DESC .
Is the issue that it is not properly sorting answers that happened on the same day? If so, it is because a DATE has a granularity of a day. You probably want the column to be a DATETIME to also store the time (with a granularity of a second).
精彩评论