Ordering results based on another table

[jamiesage123]

Hello all,

 

I've been trying to create a query which returns the newest results based on another table row.

 

Basically, i'm trying to create a small forums, where the topic with the newest reply is always at the top.

 

Here is what my tables look like:

 

(I have renamed a few rows, just so it isn't the same as my database)

 

forums

 

id | oid | author | title | detail | views | Tdate | replies

 

(oid is a persons group; they have to have the same oid to see a particular forum/topic)

(Tdate is a 'CURRENT_TIMESTAMP')

 

 

forums_replies

 

rid | topicid | oid | author | message | date

 

(date is a unix timestamp)

 

At the moment, i've tried using a few queries which haven't worked. The closest i've gotten is this:

 

SELECT `forums`. * , `forums_replies`.`date`, `forums_replies`.`orgid`, `forums_replies`.`topicid`
FROM `forums`
INNER JOIN `forums_replies` ON `forums_replies`.`oid` = `forums`.`oid`
AND `forums`.`id` = `forums_replies`.`topicid`
GROUP BY `forums`.`id`
ORDER BY  `forums_replies`.`date` DESC

 

It does work when they is only a few results in the forums_replies table. However, it seems like it gets mixed up when they is over 2 results in their, causing it not to be ordered in the correct way. It also shows duplicated results.

 

Any help will be really appreciated. 

[Barand]

Here's a similar post

 

http://www.phpfreaks.com/forums/index.php?topic=358093.msg1692758#msg1692758

[jamiesage123]

Ahh, thank you so much!