Ordering by posts in another table

[Jarkoza]

I have created a feature where users can start new topics (similar to forums).At the moment on a page, the query for the topics are as follows:\[code\]$q = "SELECT ".TBL_COMMUNITYTHREADS.".title, ".TBL_COMMUNITYTHREADS.".id, ".TBL_COMMUNITYTHREADS.".date, ".TBL_COMMUNITYTHREADS.".author, ".TBL_USERS.".username FROM ".TBL_COMMUNITYTHREADS." INNER JOIN ".TBL_USERS." ON ".TBL_COMMUNITYTHREADS.".author = ".TBL_USERS.".id WHERE type = '$type' ORDER BY date DESC LIMIT $offset, $rowsperpage ";\[/code\]The tables are constants and the offset and rowsperpage are variables passed in to limit how many posts are on a page.At the moment though, all the topics are ordered by the date.I want them to be ordered by the latest response. Similarly to forums, when the reponse inside the topic is newest, that topic will go to the top.The topics are stored in tbl_communitythreads and the replies in tbl_communityreplies.How can I ordered them by the latest repsonse.They are linked by the threadid in tbl_communityreplies. Also in that one is the date column.Thankyou for reading, I just cant think of how to do this.