Changing query ordering based on whether a variable equals 1 or 0

Question

Hello,

For the query below, I would like to change the way the results are ordered.

Right now, the results are ordered by ORDER BY most_recent DESC. I would like to keep this ordering, but I would like any row where s.topten = 1 to be ordered above rows where s.topten = 0.

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 most_recent DESC
LIMIT $offset, $rowsperpage";   

Answer 1

You can do this with a CASE statement in your ORDER BY clause:

ORDER BY case when s.topten = 1 then 0 else 1 end, 
 most_recent DESC