There are comments table:

 id | cid | time | text ------------------------- 1 | 9 | 1000 | текст 2 | 9 | 1001 | текст2 3 | 10 | 1000 | текст3 4 | 10 | 1003 | текст4 5 | 11 | 1088 | текст5

As a result, you need to get only the text and cid fields for MAX(time) for each cid (id: 2, 4, 5)

Example result:

  cid | text ------------ 9 | текст2 10 | текст4 11 | текст5

How to make the appropriate SELECT query?

  • it was already somewhere - BOPOH
  • already solved the problem like this: SELECT * FROM (SELECT * FROM comments ORDER BY time DESC) as tbl GROUP BY tbl.cid - ultrabatya
  • What if the table contains multiple MAX(time) values ​​for the same cid ? - Mark Shevchenko
  • not at all optimal, how many records do you have now? make there a couple of tens of millions - there will already be problems - BOPOH
  • If you can understand what is being done in this matter , then you can write a normal query. But somewhere there was another question (which is more suited to yours than the first one), where it was normally written - BOPOH

2 answers 2

  1. the response with the subquery is erroneous, it will not always work correctly.

  2. the final decision depends on whether the table can contain several identical max (time) for one cid and whether all or only one is needed.

  3. There are at least 5 ways to solve your problem, as described in the article Grouping in MySQL

    The response from the comments from the author ultrabatya

     SELECT * FROM (SELECT * FROM comments ORDER BY time DESC) as tbl GROUP BY tbl.cid