Making a query ordering condition dependent on the age of a variable

Question

Hello,

The query below works well. However, I would like to modify it.

I would like the ordering by when s.topten = 1 then 0 else 1 end to only be done when s.datesubmitted was less than 24 hours from the present time.

How can I do this?

Thanks in advance,

John

$sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, 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
INNER JOIN login l ON s.loginid = l.loginid
LEFT OUTER JOIN comment c ON s.submissionid = c.submissionid
GROUP BY s.submissionid
ORDER BY case when s.topten = 1 then 0 else 1 end, most_recent DESC 
LIMIT $offset, $rowsperpage";   

Answer 1

Add another WHEN clause that checks the interval and results in -1.

Answer 2

Use:

    SELECT s.loginid, s.title, s.url, s.displayurl, 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 CASE 
             WHEN s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 DAY) AND s.topten = 1 THEN 0 
             WHEN s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 DAY) AND s.topten != 1 THEN 1
             ELSE 2 
           END, most_recent DESC 
     LIMIT $offset, $rowsperpage