CODEWITHSUNDEEP
mysqlsql
user979331
user979331

Reputation: 11851

SQL Condition in ORDER BY clause

I have this query here:

SELECT posts.id, username, cover, audio, title, postDate, commentsDisabled,
       MAX(postClicks.clickDate) as clickDate,
       COUNT(*) as ClickCount
FROM postClicks INNER JOIN
     posts
     ON posts.id = postClicks.postid INNER JOIN
     users
     ON users.id = posts.user
WHERE posts.private = 0
GROUP BY postClicks.postid
ORDER BY ClickCount
LIMIT 5

This query gets me the top 5 results ORDER BY Count which is ClickCount. Each postClicks in my database has a clickDate what I am trying to do now is with the 5 results I get back, put them in order by ClickCount within the past 24 hours, I still need 5 results, but they need to be in order of ClickCount with 24 hour period.

I use to have this in the where clause:

postClicks.clickDate > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

But after the 24 hour period I would not get 5 results, I need to get 5 results.

My question is, can I put a condition or case in my order by clause?

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

You cannot put a condition in the ORDER BY in this query, because that would affect the LIMIT. Instead, you can use a subquery:

SELECT pc5.*
FROM (SELECT posts.id, username, cover, audio, title, postDate, commentsDisabled,
             MAX(postClicks.clickDate) as clickDate,
             COUNT(*) as ClickCount,
             SUM(postClicks.clickDate > DATE_SUB(CURDATE(), INTERVAL 1 DAY)) as clicks24hours
      FROM postClicks INNER JOIN
           posts
           ON posts.id = postClicks.postid INNER JOIN
           users
           ON users.id = posts.user
      WHERE posts.private = 0
      GROUP BY postClicks.postid
      ORDER BY ClickCount
      LIMIT 5
     ) pc5
ORDER BY clicks24hours DESC;

Upvotes: 1

Related Questions