SQL question on a join issue (can't use a subquery)

[Fumigator]

My stupid host is using MySQL 4.0. I will be changing hosts, but for now I would really like to get this query working. I would rather not split the query into two queries because of the pagination class I am using.

This is a simple message board. I want to select and list threads in the order of the timestamp on the most recent post in each thread (descending). With a subquery I could do it like this:


SELECT t.title_ds,
t.author_id,
t.thread_id,
date_format(t.created_ts, '%%b %%c %%Y, %%l:%%i %%p') as created_ts_f,
t.status_cd,
t.views_nr,
post.created_ts as post_ts
FROM mb_thread_tbl as t,
mb_post_tbl as post
WHERE t.category_id = {$catID}
AND t.status_cd in ('ACTIVE', 'LOCKED')
AND post.post_id = (SELECT post_id FROM mb_post_tbl as post2 WHERE post2.thread_id = t.thread_id ORDER BY created_ts desc LIMIT 1)
ORDER BY post_ts desc
LIMIT {$pager->get_limit()}


I can't for the life of me figure out how to do this without the subquery. Does anyone know of a clever method to extract that most recent post's timestamp (and order the results by it) within this query?

[Fumigator]

Writing the problem down seems to have helped! I have tentatively figured out a solution for this. I added a GROUP BY clause on the post's timestamp and then sort on max(timestamp) like this:


SELECTt.title_ds,
t.author_id,
t.thread_id,
date_format(t.created_ts, '%b %e %Y, %l:%i %p') as created_ts_f,
t.status_cd,
t.views_nr,
max(post.created_ts) as post_ts,
FROM mb_thread_tbl as t,
mb_post_tbl as post
WHERE t.category_id = {$catID}
AND t.status_cd in ('ACTIVE', 'LOCKED')
AND t.author_id = p.person_id
AND t.thread_id = post.thread_id
GROUP BY post.thread_id
ORDER BY post_ts desc
LIMIT {$pager-get_limit()}


I do wonder if this is a mis-use of the GROUP BY clause; I know sometimes GROUP BY seems to solve an issue but it causes issues as well... for now I'll roll with it and test it a lot.