"Uwe C. Schroeder" <uwe@xxxxxxxxx> writes: > I want to get a list looking like > > forum id thread_id post_id > 1 6 443 > 2 9 123 > 3 3 557 ... > It all boils down to me not being able to come up with a query that gives me > the latest post per forum_id. In a situation like this I would probably denormalize the tables slightly by adding a form_id key to the individual posts. That would make it hard to ever move a thread from one forum to another, though not impossible, but would help in this case as well as any other time you want to do an operation on all posts in a forum regardless of thread. If you add that column then you could index <form_id,date> and get the result you're looking for instantly with a DISTINCT ON query (which is a Postgres SQL extension). SELECT DISTINCT ON (form_id) forum_id, thread_id, post_id FROM thread ORDER BY forum_id, date DESC (actually you would have to make the index on <form_id, date DESC> or make both columns DESC in the query and then re-order them in an outer query) Alternatively you could have a trigger on posts which updates a last_updated field on every thread (and possibly a recent_post_id) then you could have a query on forums which pulls the most recently updated thread directly without having to join on form_post at all. That would slow down inserts but speed up views -- possibly a good trade-off for a forum system. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!