Nested data for forum

Got myself stuck...

Us left and right limits in nested set model to store forum posts and I need to select threads ordered by latest reply.

Relevant table structure:

id  lft  rght date
1   1    4    2011-01-01
2   5    8    2011-01-02
3   6    7    2011-01-04
4   2    3    2011-01-05

so from that I need to get

id last_reply latest
1  4          2011-01-05
2  6          2011-01-04

any help would be very much appreciated.

Solution

SELECT  pt.*, MAX(pc.date) AS latest
FROM    post pt
JOIN    post pc
ON      pc.lft BETWEEN pt.lft AND pt.rgt
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    post pu
        WHERE   pt.lft BETWEEN pu.lft AND pu.rgt
        )
GROUP BY
        pt.id
ORDER BY
        latest DESC

Nested sets is not good for selecting the depth level so this is not going to be efficient.

I would suggest storing the thread started id along with each post and create a composite index on (starter, date).

This way, you could just use:

SELECT  pt.*,
        MAX(date) AS latest
FROM    post pu
JOIN    post pt
ON      pt.id = pu.starter
GROUP BY
        pu.starter
ORDER BY
        latest DESC

which would be much faster.