CODEWITHSUNDEEP
phpsqlmysqlgreatest-n-per-group
Blood
Blood

Reputation: 53

MySQL Query Design for Latest Post per Forum

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

Upvotes: 5

Views: 694

Answers (3)

Chris Henry
Chris Henry

Reputation: 12010

Alternative to a JOIN, you can add a column to your forums_forums table with a name like last_updated. On every posting to a thread, simply run an additional UPDATE like.

UPDATE forums SET last_updated = NOW()

Then, to get the forums by the order of forums, your SELECT becomes much simpler, and performant.

SELECT * FROM forums_forum ORDER BY last_updated DESC

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562601

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
);

Upvotes: 3

helle
helle

Reputation: 11660

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?

Upvotes: 1

Related Questions