title: get the last record of table in select query
body: <p>This is a follow up on another problem i had with <a href="https://stackoverflow.com/questions/717720/getting-the-last-record-inserted-into-a-select-query">getting-the-last-record-inserted-into-a-select-query</a></p>
  
  <p>I am trying to edit a query that Andrea was kind enough to help me with yesterday which works fine for one page but I am trying to create a similar query without much luck.</p>
  
  <p>What I need to to is for every board display the board name, the count of topics and messages linked to that board and the user, topic and date of the last message (which does work)</p>
  
  <p>What i need is to get the board name, the topic and message count</p>
  
  <p>This is my table structure</p>
  
  <pre><code>CREATE TABLE `boards` (
    `boardid` int(2) NOT NULL auto_increment,
    `boardname` varchar(255) NOT NULL default '',
    PRIMARY KEY  (`boardid`)
  );
  
  CREATE TABLE `messages` (
    `messageid` int(6) NOT NULL auto_increment,
    `topicid` int(4) NOT NULL default '0',
    `message` text NOT NULL,
    `author` varchar(255) NOT NULL default '',
    `date` datetime(14) NOT NULL,
    PRIMARY KEY  (`messageid`)
  );
  
  CREATE TABLE `topics` (
    `topicid` int(4) NOT NULL auto_increment,
    `boardid` int(2) NOT NULL default '0',
    `topicname` varchar(255) NOT NULL default '',
    `author` varchar(255) NOT NULL default '',
    PRIMARY KEY  (`topicid`)
  );
  </code></pre>
  
  <p>and the query I have come up with based on the query that Andrea did for me.  What this query outputs in the boardname, the number of topics and messages (which says 1 even though there are 5), the topic author and messagecount (which isn't needed), the author and date of the last post (which is needed) but not the topic name which is needed</p>
  
  <pre><code>SELECT b.boardname, count( DISTINCT t.topicname ) AS topics, count( lm.message ) AS message, t.author as tauthor, 
  
        (select count(message) from messages m where m.topicid = t.topicid) AS messagecount, 
         lm.author as lauthor, lm.date
    FROM topics t
    INNER JOIN messages lm
      ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2)
    INNER JOIN boards b
      ON b.boardid = t.boardid
    GROUP BY t.topicname
  </code></pre>
  
  <p>This my original query that does what I wanted but get the first post, not the last</p>
  
  <pre><code>SELECT b.boardid, b.boardname, count( DISTINCT t.topicname ) AS topics, count( m.message ) AS message, m.author AS author, m.date AS date, t.topicname AS topic
  FROM boards b
  INNER JOIN topics t ON t.boardid = b.boardid
  INNER JOIN messages m ON t.topicid = m.topicid
  INNER JOIN (
  
  SELECT topicid, MAX( date ) AS maxdate
  FROM messages
  GROUP BY topicid
  ) test ON test.topicid = t.topicid
  GROUP BY boardname
  ORDER BY boardname
  </code></pre>
  
  <p>any help with this much appreciated</p>