CODEWITHSUNDEEP
phpmysqlsqlsql-order-by
John
John

Reputation: 4944

ORDER BY / CASE Combination Causing MySQL Query to Not Work

The query below is returning a blank page. It's caused by the ORDER BY and CASE combination, because the rest of the query works fine with something less complicated.

Does anyone see any glaring errors in this part:

ORDER BY CASE 
           WHEN s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN s.points 
           WHEN s.datesubmitted < DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN most_recent 
         END, DESC

Code:

$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 CASE 
                    WHEN s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN s.points 
                    WHEN s.datesubmitted < DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN most_recent 
                  END, DESC                  
            LIMIT $offset, $rowsperpage";

Upvotes: 0

Views: 1459

Answers (2)

M.R.
M.R.

Reputation: 4827

You want it without the comma after your 'case' statement:

ORDER BY CASE
      WHEN s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN s.points
      WHEN s.datesubmitted < DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN most_recent
END DESC

Or, to understand it better, you could possibly surround it with parens...

ORDER BY (CASE
      WHEN s.datesubmitted > DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN s.points
      WHEN s.datesubmitted < DATE_SUB(NOW(), INTERVAL 1 HOUR) THEN most_recent
END) DESC

...so it's like a column, and then you put the 'DESC' keyword after it designate that you want to order in descending order.. (no comma needed)

Upvotes: 2

Paul Z
Paul Z

Reputation: 872

Pretty sure the comma between END and DESC is not correct. You might try wrapping the whole CASE expression in parentheses, if you're worried that ORDER BY is getting the syntax confused.

I would put this large confusing expression in the select list and just order on that, though? Is there a reason you definitely can't change the select list?

Upvotes: 4

Related Questions