Ordering by two fields

Question

Hello,

The input below sorts submissions by a timestamp field called "datesubmitted" in reverse chronological order. This field is in a MySQL table called "submission."

Another MySQL table "comment" has another timestamp field called "datecommented."

Each submission has only one "datesubmitted" but it could have several comments, each with a different "datecommented."

How could I sort the submissions by "datesubmitted" and each one's last "datecommented"? In other words, I want the top of this list to show either the most recently submitted entry or the entry with the most recent comment, whichever occurred most recently.

Thanks in advance,

John

$sqlStr = "SELECT 
                s.loginid
                ,s.title
                ,s.url
                ,s.displayurl
                ,s.datesubmitted
                ,l.username
                ,s.submissionid
                ,COUNT(c.commentid) countComments
             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 
                 s.datesubmitted DESC
             LIMIT 
                 10";           

$tzFrom = new DateTimeZone('America/New_York'); 
$tzTo = new DateTimeZone('America/Phoenix'); 



// echo $dt->format(DATE_RFC822); 


$result = mysql_query($sqlStr);

$arr = array(); 
echo "<table class=\"samplesrec\">";
while ($row = mysql_fetch_array($result)) { 
    $dt = new DateTime($row["datesubmitted"], $tzFrom); 
    $dt->setTimezone($tzTo);
    echo '<tr>';
    echo '<td class="sitename1"><a href="http://www.'.$row["url"].'" TARGET="_blank">'.$row["title"].'</a>  <div class="dispurl">'.$row["displayurl"].'</div></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2name">Submitted by <a href="http://www...com/.../members/index.php?profile='.$row["username"].'"&gt;'.$row["username"].'&lt;/a&gt; on '.$dt->format('F j, Y &\nb\sp &\nb\sp g:i a').'</td>';

    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2"><a href="http://www...com/.../comments/index.php?submission='.$row["title"].'&amp;submissionid='.$row["submissionid"].'&amp;url='.$row["url"].'&amp;countcomments='.$row["countComments"].'&amp;submittor='.$row["username"].'&amp;submissiondate='.$row["datesubmitted"].'&amp;dispurl='.$row["displayurl"].'"&gt;'.$row["countComments"].' comments</a></td>';
    echo '</tr>';
    }
echo "</table>";    

Answer 1

you can order by multiple columns by separating them with a comma. So you could do ...ORDER BY s.datesubmitted DESC, c.datecommented DESC. Also if both are the same direction (asc/desc) you can just say it once at the end. The query will be ordered by the first column in the list and under that the next column so it is sorted in groups.

Answer 2

SELECT s.loginid, s.title, s.url, s.displayurl, s.datesubmitted, l.username,
  s.submissionid, 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 10

Answer 3

It sounds like you want to ORDER BY conditionally, depending on whichever date is higher. Include this ORDER BY.

ORDER BY CASE WHEN datesubmitted > datecommented 
         THEN datesubmitted 
         ELSE datecommented END  DESC