[PHP & MySQL] Sorting Threads by Last Post

[punky_munky]

Hey guys,

More of a theory question rather than wanting some code written for me. The project I'm working on at the moment has a small threaded discussion page. I've got most of that working no problems, the bit that's really got me stuck though is sorting the threads by the time of the last post. I've included a bit of SQL to show the fields I have for the related tables so far.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL auto_increment,
  `post` text NOT NULL,
  `date_time` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `thread_id` int(11) NOT NULL,

CREATE TABLE `threads` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(256) NOT NULL default '',
  `date_time` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `forum_id` int(11) NOT NULL,

In my head I can think of two options:

a) Write a complex SQL query which I can't get my head around

b) Add a 'last_post_id' field to the thread table and update it whenever a post is made. That wouldn't be difficult at all but it seems a bit hacky (although I'm not that fussy).

Opinions?

 

[punky_munky]

That selects the first post from the thread though, not the last. Thanks for your input though

 

[punky_munky]

Nevermind, MAX(posts.date_time) AS last_post seemed to fix it

 

[punky_munky]

Thanks your help. It seems so obvious now.