title: SQL: Limit rows linked to each joined row
body: <p>I've certain situation that requires certain result set from MySQL query, let's see the current query first &amp; then ask my question:</p>
  
  <pre><code>SELECT 
      thread.dateline AS tdateline, post.dateline AS pdateline, MIN(post.dateline)
      FROM thread AS thread
          LEFT JOIN post AS post ON(thread.threadid = post.threadid)
          LEFT JOIN forum AS forum ON(thread.forumid = forum.forumid)
      WHERE post.postid != thread.firstpostid
          AND thread.open = 1
          AND thread.visible = 1
          AND thread.replycount &gt;= 1
          AND post.visible = 1
          AND (forum.options &amp; 1)
          AND (forum.options &amp; 2)
          AND (forum.options &amp; 4)
          AND forum.forumid IN(1,2,3)
      GROUP BY post.threadid
      ORDER BY tdateline DESC, pdateline ASC
  </code></pre>
  
  <p>As you can see, mainly I need to select dateline of threads from 'thread' table, in addition to dateline of the second post of each thread, that's all under the conditions you see in the WHERE CLAUSE. Since each thread has many posts, and I need only one result per thread, I've used GROUP BY CLAUSE for that purpose.</p>
  
  <p>This query will return only one post's dateline with it's related unique thread.</p>
  
  <p><strong>My questions are:</strong></p>
  
  <ol>
  <li>How to limit returned threads per
  each forum!? Suppose I need only 5
  threads -as a maximum- to be
  returned for each forum declared in
  the WHERE CLAUSE 'forum.forumid
  IN(1,2,3)', how can this be
  achieved.</li>
  <li>Is there any recommendations for
  optimizing this query (of course
  after solving the first point)?</li>
  </ol>
  
  <p><strong>Notes:</strong></p>
  
  <ul>
  <li>I prefer not to use sub-queries, but
  if it's the only solution available
  I'll accept it. Double queries not
  recommended. I'm sure there's a smart
  solution for this situation.</li>
  <li>I'm using MySQL 4.1+, but if you know
  the answer for another engine, just
  share.</li>
  </ul>
  
  <p><strong>Appreciated advice in advance :)</strong></p>