MySQL Query Design for Latest Post per Forum

Question

i have this 3 tables

forums_forum

+-----+--------+-------------+-------+-----+
| fid | name   | description | index | cid |
+-----+--------+-------------+-------+-----+
|  36 | gdghdf | hjghj       |    54 |   5 |
|  45 | yutuy  | iuyi        |    99 |   6 |
+-----+--------+-------------+-------+-----+

forums_threads

+----+-----+-----+-------+-------+------+-----------+------+
| id | tid | fid | moved | mfrom | view | important | lock |
+----+-----+-----+-------+-------+------+-----------+------+
|  1 |   4 |  36 |     0 | NULL  |    0 |         0 |    0 |
|  2 |  12 |  36 |     0 | NULL  |    7 |         0 |    0 |
|  3 |   9 |  15 |     0 | NULL  |    0 |         0 |    0 |
+----+-----+-----+-------+-------+------+-----------+------+

forums_posts

+----+-------+--------+--------+---------------------+--------+--------+-----+
| id | title | detail | author | date                | edited | editby | tid |
+----+-------+--------+--------+---------------------+--------+--------+-----+
|  1 | asfsd | sdfsd  |      1 | 2010-07-01 21:31:29 |      0 | NULL   |   4 |
+----+-------+--------+--------+---------------------+--------+--------+-----+

I'm trying to create query which return result -> for every unique 'fid', one row from 'forums_posts' (ORDER BY 'date').

forums_forum.fid = forums_threads.fid forums_threads.tid = forums_posts.tid

Thanks

Answer 1

This is the venerable greatest-n-per-group problem that comes up frequently on Stack Overflow. Here's a solution given your tables:

SELECT p.* FROM forums_posts p JOIN forums_threads t ON p.tid = t.tid
WHERE NOT EXISTS (
    SELECT * FROM forums_posts p2 JOIN forums_threads t2 ON p2.tid = t2.tid
    WHERE t.fid = t2.fid AND p.date < p2.date
);

Answer 2

Well i suggest some JOINs for you.

SELECT C.date, C.title, A.name
FROM forums_forum A 
   JOIN forums_threads B ON A.fid=B.fid 
   JOIN forums_posts C ON B.tid=C.tid
ORDER BY C.date DESC LIMIT 1

or .. not tested:

SELECT MAX(c.date), C.date, C.title, A.name
FROM forums_forum A 
   JOIN forums_threads B ON A.fid=B.fid 
   JOIN forums_posts C ON B.tid=C.tid
LIMIT 1

;-)

p.s. you might get troubles by naming a column "date" as well as "index" and "view", better use other names.

p.p.s. are there fids (i assume this is your primary key and should be unique) whiche occure more then once?