Getting the last record inserted into a select query

Question

I am creating a small message board and I am stuck

I can select the subject, the original author, the number of replies but what I can't do is get the username, topic or date of the last post.

There are 3 tables, boards, topics and messages.

I want to get the author, date and topic of the last message in the message table. The author and date field are already fields on the messages table but i would need to join the messages and topics table on the topicid field.

this is my query that selects the subject, author, and number of replies

SELECT t.topicname, t.author, count( message ) AS message
  FROM topics t
  INNER JOIN messages m
    ON m.topicid = t.topicid
  INNER JOIN boards b
    ON b.boardid = t.boardid
  WHERE b.boardid = 1
  GROUP BY t.topicname

Can anyone please help me get this finished?

This is what my tables look like

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

Answer 1

You can use a subselect. Eg.:

select * from messages where id = (select max(id) from messages)

edit: And if you identify the newest record by a timestamp, you'd use:

select * from messages where id = (
  select id
  from messages
  order by post_time desc
  limit 1)

Answer 2

If you want to get the latest entry in a table, you should have a DateTime field that shows when the entry was created (or updated). You can then sort on this column and select the latest one.

But if your id field is a number, you could find the highest. But I would recommend against this because it makes many assumptions and you would be fixed to numerical ids in the future.

Answer 3

if your SQL supports the LIMIT clause,

SELECT m.author, m.date, t.topicname FROM messages m 
JOIN topics t ON m.topicid = t.topicid 
ORDER BY date desc LIMIT 1

otherwise:

SELECT m.author, m.date, t.topicname FROM messages m 
JOIN topics t ON m.topicid = t.topicid 
WHERE m.date = (SELECT max(m2.date) from messages m2)

EDIT: if you want to combine this with the original query, it has to be rewritten using subqueries to extract the message count and the date of last message:

SELECT t.topicname, t.author, 
      (select count(message) from messages m where m.topicid = t.topicid) AS messagecount, 
       lm.author, 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
  WHERE b.boardid = 1
  GROUP BY t.topicname

also notice that if you don't pick any field from table boards, you don't need the last join:

SELECT t.topicname, t.author, 
      (select count(message) from messages m where m.topicid = t.topicid) AS messagecount, 
       lm.author, lm.date
  FROM topics t
  INNER JOIN messages lm
    ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2)
  WHERE t.boardid = 1
  GROUP BY t.topicname

EDIT: if mysql doesn't support subqueries in the field list, you can try this:

SELECT t.topicname, t.author, mc.messagecount, lm.author, lm.date
  FROM topics t
  JOIN (select m.topicid, count(*) as messagecount from messages m group by m.topicid) as mc 
    ON mc.topicid = t.topicid
  JOIN messages lm
    ON lm.topicid = t.topicid AND lm.date = (SELECT max(m2.date) from messages m2)
  WHERE t.boardid = 1
  GROUP BY t.topicname

Answer 4

With MySQL this should work:

SELECT author, date, topicname as topic FROM messages LEFT JOIN topics ON messages.topicid = topics.topicid ORDER BY date DESC, LIMIT 0, 1;