Need help with query

[Leveler]

Hi,

 

I have following query to fetch 10 newest post from forum (phpBB3). MySQL version is 5.0.45.

 

SELECT post_id, post_subject, post_text, post_time
FROM phpbb_posts
WHERE (
forum_id NOT 
BETWEEN 9 
AND 14 
)
AND NOT (
forum_id =23
OR forum_id =6
)
ORDER BY post_id DESC 
LIMIT 0 , 10

 

This one works quite well and excludes forums 6, 9-14 and 23 as it should. This returns something like this:

 

topic_idpost_idpost_subjectpost_textpost_time

44299Re: AAAA[bLOB - 627 bytes]1204975985

104298Re: BBBB[bLOB - 334 bytes]1204975267

25297Re: CCCC[bLOB - 92 bytes]1204974116

23296Re: DDDD[bLOB - 379 bytes]1204973223

25294Re: CCCC[bLOB - 178 bytes]1204969208

25293CCCC[bLOB - 81 bytes]1204968393

and so on...

 

However, I would like to have only the newest post from each topic: no same topic_id twice in result. In this example, the last 2 rows should be removed. I tried a lot of different ways with DISTINCT and GROUP BY, but couldn't make it work.

 

Help would be really appreciated.

[Barand]

try (untested)

SELECT topic_id, post_id, post_subject, post_text, post_time
FROM phpbb_posts p
    INNER JOIN (SELECT topic_id, MAX(post_time) as latest FROM phpbb_posts GROUP BY topic_id) a x
    ON p.topic_id = x.topic_id AND p.post_time = x.latest
WHERE 
    p.forum_id NOT IN (6,9,10,11,12,13,14,23)
ORDER BY p.post_id DESC 
LIMIT 0 , 10

[Leveler]

Thank you very much! With few minor fixes (topic_id was ambiguous) I got it to work.

 

SELECT topic_id, post_id, post_subject, post_text, post_time
FROM phpbb_posts p
INNER JOIN (
SELECT topic_id AS t_id, MAX( post_time ) AS latest
FROM phpbb_posts
GROUP BY topic_id
) AS x ON p.topic_id = x.t_id
AND p.post_time = x.latest
WHERE p.forum_id NOT 
IN ( 6, 9, 10, 11, 12, 13, 14, 23 ) 
ORDER BY p.post_id DESC 
LIMIT 0 , 10