Selecting a row corresponding to a field returned with MIN()

[Goldeneye]

Here's the structures of my two tables (using MySQL build 5.0.51a):

CREATE TABLE `boardtopics` (
  `topicid` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(80) character set ascii NOT NULL,
  `board` int(11) NOT NULL default '0',
  `pinned` tinyint(1) unsigned NOT NULL default '0',
  `archived` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`topicid`,`board`),
  KEY `topiclist` (`board`,`archived`,`userid`),
  FULLTEXT KEY `topicsearch` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;

CREATE TABLE `commentary` (
  `associd` mediumint(10) NOT NULL,
  `input` longtext NOT NULL,
  `from` varchar(21) NOT NULL,
  `userid` mediumint(9) NOT NULL,
  `deleted` tinyint(1) unsigned NOT NULL default '0',
  `ip` bigint(10) NOT NULL,
  `time` bigint(10) NOT NULL,
  `type` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`associd`,`userid`,`time`),
  KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

What I'm trying to accomplish is to select the field-values `commentary`.`from` and `commentary`.`userid` of the row corresponding with MIN(commentary`.`time`).

 

What I have attempted was this query...

SELECT `topicid`, `title`, `pinned`, `from`, `commentary`.`userid`, MIN(`commentary`.`time`) as `created`
FROM `boardtopics`
JOIN `commentary`
ON `commentary`.`associd`=`boardtopics`.`topicid`
WHERE `board`=1 AND `archived`=0 AND `commentary`.`type`=4
GROUP BY `title`
ORDER BY `pinned` DESC, MAX(`commentary`.`time`) DESC

But it returns the field values `commentary`.`from` and `commentary`.`userid` corresponding with MAX(`commentary`.`time`).

 

I tried ASC for "SORT BY MAX(`commentary`.`time`)" I'm just uncertain how to this. I've queried a search engine for tutorials but no useful results came up.

 

So, how can I reverse this?

[.josh]

bump

[fenway]

Usually, you would use a derived table to grab the min(), and then join it back to the primary table.

[Goldeneye]

A derived table? You mean a temporary table? All the examples I looked at of how to do this sort of thing never completely worked for me. I don't want to have to create an entire table to get rid of three rows in my `boardtopics` table. I'm thinking that there isn't anyway to do this with how my tables are currently set up (as outlined in my original post). I know if I keep it the way I have it now (with 3 fields holding topic-creator, the userid of that topic-creator, and the creation-date) it will work just as I want it to.

 

The reason I want to grab the row out of `commentary` corresponding to the topic-id with the smallest UNIX-timestamp is to simply stream-line my database structure. I know the differences are negligible, performance-wise, I just like to keep the management of fields in queries small and remove unnecessary fields.