Sort topics by most recent comment

[mikwit]

okay so I have two tables, one table has the topics, while the other has all the comments for all the topics.

main                                     comments                
________________               __________________
|number|text|date|             |reply to|time|date|
|______|____|____|             |__________________|

Currently, when I query for topics, I get number, text, time + count of comments whose replyto = number through this query.

SELECT `number` , `date` , `text`, b.CommentCount 
	FROM main a
	LEFT OUTER JOIN (
	SELECT replyto, COUNT( `text` ) AS CommentCount
	FROM `comments`
	GROUP BY replyto
	)b ON `number` = b.replyto
	ORDER BY `date` DESC LIMIT '.$lowlim.', 15

Instead of ORDER BY date (of the post), I would like to figure out/find a way to order by most recent date of comment.  Any help or guidance would be greatly appreciated

[callesson]

You could change the name of Comments, date to Date2 instead.

then order i by Date2 ? or.. ?

 

              comments             

        _______________

        |reply to|time|date2|

        |_______________|

[mikwit]

I don't get what you mean, I would think you have to merge in the date of the most recent comment of that post and then sort by that

[ignace]

SELECT number, text, reply_to, time, m.date AS main_date, c.date AS comment_date,
       count(*) AS comments_count
FROM main m
JOIN comments c ON m.number = c.reply_to
GROUP BY m.number
ORDER BY c.date DESC

[mikwit]

So I've gotten it to work sort of

SELECT `number` , `school` , `date` , `text` , `category` , `likes` , `dislikes` , `md5` , `banned` , b.CommentCount , `name` , `title`, m.last_date 
	FROM main a
	LEFT OUTER JOIN (
	SELECT replyto, COUNT( `text` ) AS CommentCount
	FROM `comments`
	GROUP BY reply_to
	)
    b ON `number` = b.reply_to
	left outer JOIN ( 
    select reply_to, max(`date`) AS last_date 
    from `comments`
	GROUP BY reply_to)
    m on `number` = m.reply_to
    NATURAL JOIN college_list
	WHERE school = 21
	ORDER BY last_date 

This works, but I get null fields and it messes with the sorting... I've tried a max(ifnull(`date`,[date from post] )) but I don't know how to call the date of the post.  So is there there a way to do Order by maximum(last_date,date) ??