CODEWITHSUNDEEP
mysql
Zlug
Zlug

Reputation: 384

Getting grouped post where max time is lower than defined

I'm working on a news feed that needs to be able to figure out posts based on comments, while that in itself is simple enough I can't figure out how to only get "posts" where the newest comment is older than a specific time.

In order to demonstrate, here is an overly simplified version of my data:

+----+---------+------------+
| id |  Module |    Time    |
+----+---------+------------+
|  1 | Forum   | 1343739050 |
|  2 | Gallery | 1343739150 |
|  3 | Gallery | 1343739250 |
|  4 | Forum   | 1343739350 |
+----+---------+------------+

To get the five most recently commented modules i would simply use

SELECT module, max(time) FROM `comments` GROUP BY module ORDER BY max(time) DESC LIMIT 0, 5

which will return

+---------+------------+
|  Module |  max(Time) |
+---------+------------+
| Forum   | 1343739350 |
| Gallery | 1343739250 |
+---------+------------+

Exactly what I want. The problem arises when I only want to get modules where the most resent comment is older than a specific time. For instance older than 1343739350 (the time of the comment with id 4), but is i simply throw in a where clause it will still return Forum as it has one comment that matches it.

I made a wild guess and tryed

SELECT module, max(time) FROM `comments` GROUP BY module WHERE max(time) < 1343739350 ORDER BY max(time) DESC LIMIT 0, 5

That however turned out to be wishful thinking on my part.

So question is, how do I write a mysql command that can do that?

Upvotes: 2

Views: 30

Answers (1)

John Woo
John Woo

Reputation: 263813

When dealing with aggregated columns, you need to use HAVING clause instead of WHERE clause,

SELECT module, max(time) 
FROM `comments` 
GROUP BY module 
HAVING max(time) < 1343739350 
ORDER BY max(time) DESC 
LIMIT 0, 5

Upvotes: 1

Related Questions