MySQL SORT BY a column from a second table

[Goldeneye]

I'm using MySQL build 5.0.51a

 

Here are the structures of my two tables

CREATE TABLE `boardtopics` (
  `topicid` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(80) character set ascii NOT NULL,
  `creator` varchar(21) character set ascii NOT NULL,
  `created` int(10) unsigned NOT NULL default '0',
  `userid` mediumint(9) unsigned NOT NULL default '0',
  `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;

 

Intertable-relations would be:

`boardtopics`.`topicid` corresponds with `commentary`.`associd`

 

These tables are for a forum I'm working on. I've decided to minimize as many redundant columns as possible by using more complex (sub)queries and joins.

 

What I'm trying to accomplish is select all the topics of a given forum (`boardtopics`.`board`) and sort them by the time of the last post in that topic (MAX(`commentary`.`time`)).

 

What I have attempted was a JOIN...

SELECT `topicid`, `title`, `creator`, `created`, `boardtopics`.`userid`, `pinned`, `closed`, `category`, `nws`, MAX(`commentary`.`time`) as `time`
FROM `boardtopics`
JOIN `commentary`
ON `commentary`.`associd`=`boardtopics`.`topicid`
GROUP BY `commentary`.`time`
WHERE `board`=1 AND `archived`=0
ORDER BY `pinned` DESC, `time` DESC"

I got the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `board`=1 AND `archived`=0 ORDER BY `pinned` DESC, `time` DESC' -- so then I attempted a subquery.

SELECT `topicid`, `title`, `creator`, `created`, `boardtopics`.`userid`, `pinned`, `closed`, MAX(`commentary`.`time`) as `time` 
FROM `boardtopics`, `commentary`
GROUP BY `topicid`
WHERE `board`=1 AND `archived`=0 AND `commentary`.`associd`=`boardtopics`.`topicid`
ORDER BY `pinned` DESC, `commentary`.`time` DESC

 

And received this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `board`=1 AND `archived`=0 AND `commentary`.`associd`=`boardtopics`.`topic'

[DavidAM]

WHERE comes before GROUP BY.  The WHERE tells what you want to get, and the GROUP BY tells how you want to see it.

[Goldeneye]

Ah! That fixed the error. The only thing now is that each row from `boardtopics` is repeated "x" times where "x" is the number of posts (which are stored in `commentary`) in that topic.

 

Edit: I fixed that, I grouped by `boardtopics`.`title` and it fixed that repetition problem. It appears to be work just as I want it to. Thank you, DavidAM!