get the last record of table in select query

Question

This is a follow up on another problem i had with getting-the-last-record-inserted-into-a-select-query

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.

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)

What i need is to get the board name, the topic and message count

This is my table structure

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`)
);

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

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

This my original query that does what I wanted but get the first post, not the last

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

any help with this much appreciated

Answer 1

You need to define "LAST", in terms of an ORDER BY clause. Once you do that, you can just reverse the direction of your order and add LIMIT 1 to the query.

Answer 2

SELECT  b.*, m.*, t,*
        (
        SELECT  COUNT(*)
        FROM    topics ti
        WHERE   ti.boardid = b.boardid
        ) AS topiccount,
        (
        SELECT  COUNT(*)
        FROM    topics ti, messages mi
        WHERE   ti.boardid = b.boardid
                AND mi.topicid = ti.topicid
        ) AS messagecount
FROM    boards b
LEFT JOIN
        messages m
ON      m.messageid = (
        SELECT  mii.messageid
        FROM    topics tii, messages mii
        WHERE   tii.boardid = b.boardid
                AND mii.topicid = tii.topicid
        ORDER BY
                mii.date DESC
        LIMIT 1
        )
LEFT JOIN
        topics t
ON      t.topicid = m.topicid