Apply WHERE after GROUP BY

Question

I have a table with elements that are part of a stream, so they have a stream identifier, each element also has a date (UNIX timestamp). So my table looks something like this: (UNIX timestamps are simplified):

+-----------------------------+ | id | date | thread_id | +-----+---------+-------------+ | 1 | 1111 | 4 | | 2 | 1333 | 4 | | 3 | 1444 | 5 | | 4 | 1666 | 5 | +-----------------------------+ 

What I want to do is select thread identifiers in which ALL elements that have the same stream identifier are less than the past date. Therefore, if I need thread identifiers where ALL elements are older than 1555 (date <1555), I would expect the thread identifier to be returned 4, not 5, although it received an element with a date less than 1555. So this is what I tried:

 SELECT * FROM table WHERE date < 1555 GROUP BY thread_id ORDER BY date DESC 

What I'm trying to do with this query is to group all the elements by the stream identifier around the element with the highest date value and from there get the elements where the date is less than 1555. But this does not work. will still return thread id 5 because it received an item older than 1555.

So, to summarize, how do I select only thread tags, where ALL elements are older than a certain date?

Thank you for your time!

Answer 1

 SELECT thread_id FROM table GROUP BY thread_id HAVING MAX(date) < 1555 

Answer 2

Use the HAVING clause. This allows you to filter aggregates, such as SUM, MAX ... Here you want to select only those id streams whose new record is older than 1555, so you write:

 SELECT * FROM table GROUP BY thread_id HAVING MAX(date) < 1555 ORDER BY date DESC