title: How can I filter an SQL query with a GROUP BY clause
body: <p>I'm trying to formulate an SQL query for a messaging system that will return a list of all threads which have a status of 'OPEN' and whose last message was not posted by a user in a certain group.</p>
  
  <p>The tables involved in the query are:</p>
  
  <pre><code>threads
  -------
  threadId
  timestamp
  subject
  status
  clientId
  
  messages
  --------
  messageId
  userId
  messagebody
  timestamp
  threadId
  
  users
  -----
  userId
  username
  groupId
  </code></pre>
  
  <p>The current query is:</p>
  
  <pre><code>SELECT threadId, timestamp, subject, status, clientId 
  FROM threads
  WHERE status='OPEN' 
  ORDER BY timestamp DESC
  </code></pre>
  
  <p>which works fine; but I now have a new requirement - if the last message in the thread was posted by a user with a <code>groupId</code> of 1 then it should NOT be in the result set.</p>
  
  <p>Including the information I need is simple enough:</p>
  
  <pre><code>SELECT threadId, timestamp, subject, status, clientId 
  FROM threads
  INNER JOIN messages USING(threadId)
  INNER JOIN users USING(userId)
  WHERE status='OPEN' 
  GROUP BY threadId
  ORDER BY timestamp DESC
  </code></pre>
  
  <p>but at this point I get stuck, because I can't figure out how to get the query to filter out threads based on the <code>messages.userId</code> with the highest <code>timestamp</code> within a particular group. I've looked at <code>HAVING</code> and all the aggregate functions and nothing seems to fit the bill.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I may not have been clear enough, so I'll try to illustrate with an example.</p>
  
  <p>Here's some example data:</p>
  
  <p><strong>threads</strong></p>
  
  <pre><code>threadId | timestamp | subject | status | clientId
  --------------------------------------------------
  1        | 1         | One     | OPEN   | 1
  2        | 10        | Two     | OPEN   | 4
  3        | 26        | Three   | OPEN   | 19
  4        | 198       | Four    | OPEN   | 100
  </code></pre>
  
  <p><strong>messages</strong></p>
  
  <pre><code>messageId | userId | messagebody | timestamp | threadId
  -------------------------------------------------------
  1         | 3      | Hello, World| 1         | 1
  2         | 1      | Hello       | 3         | 1
  3         | 1      | ^&amp;%&amp;6       | 10        | 2
  4         | 2      | Test        | 12        | 2
  5         | 4      | Hi mum      | 26        | 3
  6         | 1      | More tests  | 100       | 4
  </code></pre>
  
  <p><strong>users</strong></p>
  
  <pre><code>userId | username | groupId
  ---------------------------
  1      | Gareth   | 1
  2      | JimBob   | 2
  3      | BillyBob | 2
  4      | Bod      | 3
  </code></pre>
  
  <p>In this example dataset threads 1 and 4 should be eliminated from the query because user <code>Gareth</code> (a member of group 1) is the last one to post to them (messageId 2 in threadId 1, and messageId 5 in threadId 4). So the result set should only have the thread data (threadId, timestamp, subject, status and clientId) for threads 2 and 3.</p>
  
  <p>I've created an <a href="http://sqlfiddle.com/#!2/b9d86" rel="nofollow">SQLfiddle</a> for this test data.</p>
  
  <p>Can anyone point me in the right direction here?</p>