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

In article <200809070253(dot)15422(dot)uwe(at)oss4u(dot)com>,
"Uwe C. Schroeder" <uwe(at)oss4u(dot)com> writes:

> or maybe not and I'm just not getting it.
> So here's the scenario:

> I have 3 tables

> forum: with primary key "id"
> forum_thread: again primary key "id" and a foreign key "forum_id" referencing
> th primary key of the forum table
> forum_post: again primary key "id" with a forign key "thread_id" referencing
> the primary key of the forum_thread table

> The forum_post table also has a field "date_posted" (timestamp) with an index
> on it.

> What I need is an efficient way to create overviews (just think about a forum)
> I.e. the forum table has 3 records, one for each forum category

> I want to get a list looking like

> forum id thread_id post_id
> 1 6 443
> 2 9 123
> 3 3 557

> The trick is, that I need the latest post (by the date posted column) for each
> category (speak forum_id). Due to the keys the forum_thread table has to be
> involved.

> I've been thinking about this for hours now, but I just can't come up with a
> query that will give me 3 records, one for each category showing the latest
> post.

Try something like this:

SELECT t1.forum_id, p1.thread_id, p1.id AS post_id, p1.date_posted
FROM forum f1
JOIN forum_thread t1 ON t1.forum_id = f1.id
JOIN forum_post p1 ON p1.thread_id = t1.id
LEFT JOIN (
SELECT t2.forum_id, p2.thread_id, p2.date_posted
FROM forum_thread t2
JOIN forum_post p2 ON p2.thread_id = t2.id
) AS f2 ON f2.forum_id = f1.id AND f2.date_posted > p1.date_posted
WHERE f2.forum_id IS NULL
ORDER BY t1.forum_id