there is a table like this

CREATE TABLE messages_list ( id int(11) NOT NULL auto_increment, tstamp int(11) DEFAULT '0' NOT NULL, recipient_id int(11) DEFAULT '0' NOT NULL, sender_id int(11) DEFAULT '0' NOT NULL, content tinytext, topic_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (id) );

It stores messages from different topics (topic_id). You need to select the latest posts from each topic.

 `SELECT max(id) FROM `messages_list` group by topic_id`

- With this query, you can select the id of the last records, but how can you change the query to get all the information?

  • Need to group by topic_id - Ilya Bizunov
  • SELECT * FROM messages_list group by topic_id order by tstamp DESC this request returns the first messages from the topics - VK
  • There may be an unlimited number - VK
  • Do you want to clash or for specific topics? Or is it enough to select the first 10..100 id-snik and sort by topic? - nick_n_a
  • 2
    The meaning is the same. subquery to the same table get the latest dates in the context of topics. it's so simple: select * from table where id in(select max(id) from table group by topic_id) - Mike

1 answer 1

 SELECT * FROM `messages_list` WHERE id IN( SELECT max(id) FROM `messages_list` group by topic_id )