[RESOLVED] Query sorting issue

[bengt]

This is my query

SELECT DISTINCT t.topic_id, t.parent_id, t.root_id, t.name, t.description, t.author, t.author_host, DATE_FORMAT(t.create_dt, '%d/%m-%Y kl. %H:%i') as dato, t.modify_dt, t.forumid, t.hits  

FROM px_topics t , px_topics r
WHERE t.topic_id = r.root_id 
AND t.forumid = '$lagid' 
ORDER BY r.create_dt 
DESC;

This is a forum, and not showing in thread mode. And I want to sort the topic list on the latest entry in a thread.

This is the table from mysql

CREATE TABLE `px_topics` (
  `topic_id` int(20) NOT NULL auto_increment,
  `parent_id` int(20) NOT NULL default '0',
  `root_id` int(20) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `description` text,
  `create_dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `modify_dt` timestamp NOT NULL default '0000-00-00 00:00:00',
  `author` varchar(255) NOT NULL default '',
  `author_host` varchar(255) NOT NULL default '',
  `forumid` int(5) NOT NULL default '0',
  `hits` int(5) NOT NULL default '0',
  PRIMARY KEY  (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Anyone who know how i can modify my query to do what I want?

[bengt]

I think it has something to do with the DISTINCT attrib.
Since it rules out duplicates, so when I order it to the field I want, it skips the latest entries.

Is there someway I can rewrite the query without using distinct.

[bengt]

I solved my problem by adding an update, that updates the modify_dt in root_id