Making the ordering of a MySQL Query Depending on the Age of a Timestamp Field

For the query below, I am ordering it by ORDER BY s.points DESC, which is working fine. But I would like to change it so that it orders by s.points descending only while s.datesubmitted is within the last hour. The field s.datesubmitted is a timestamp. Otherwise, I w开发者_C百科ould like to order by most_recent descending.

How could I do this?

Thanks in advance,

John

 $sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, s.points, s.datesubmitted, l.username,
           s.submissionid, s.subcheck, s.topten, COUNT(c.commentid) countComments, 
           GREATEST(s.datesubmitted, COALESCE(MAX(c.datecommented), s.datesubmitted)) AS most_recent
      FROM submission s
      JOIN login l ON s.loginid = l.loginid
 LEFT JOIN comment c ON s.submissionid = c.submissionid
  GROUP BY s.submissionid
  ORDER BY s.points DESC 

     LIMIT $offset, $rowsperpage";

---

I would use date functions such as sub/add etc. (mysql function) http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

$sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, s.points, s.datesubmitted, l.username,
           s.submissionid, s.subcheck, s.topten, COUNT(c.commentid) countComments, 
           GREATEST(s.datesubmitted, COALESCE(MAX(c.datecommented), s.datesubmitted)) AS most_recent
      FROM submission s
      JOIN login l ON s.loginid = l.loginid
 LEFT JOIN comment c ON s.submissionid = c.submissionid
  GROUP BY s.submissionid

  ORDER BY s.points DESC, s.datesubmitted DESC
     WHERE s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 HOUR)

     LIMIT $offset, $rowsperpage";

I have nowhere to test it though.

---

MySQL permits expressions in the ORDER BY clause.

...
ORDER BY (CASE WHEN s.submitted > DATE_SUB(NOW(), INTERVAL 1 HOUR) 
  THEN s.points ELSE GREATEST(s.datesubmitted, 
                        COALESCE(MAX(c.datecommented), s.datesubmitted)) END) DESC;

If, as I suspect, you actually want the last hour's first, and then the stale ones under the different ordering, add another ORDER BY variable before this one,

(CASE WHEN s.submitted > DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN 1 ELSE 0 END)

and sort DESC on that too.