Re: A challenge for the SQL gurus out there...

"Uwe C. Schroeder" <uwe(at)oss4u(dot)com> 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!