Join tables, LIMIT 1

Question

MySQL

Hello. Can you tell me how to correctly format the SQL query to get the only and last value in the second table?

SELECT
  t1.id,
  t1.title,
  t2.user AS `last_user`,
  t2.datetime AS `last_date`
FROM
  `topics` AS t1 INNER JOIN
  `comments` AS t2 ON (t2.id_topic = t1.id)
GROUP BY
  t1.id DESC

I want to get the last comment like this:

SELECT t2.datetime, t2.user FROM `comments` AS t2 WHERE t2.id_topic = t1.id ORDER BY t2.id DESC LIMIT 1

Answer 1

For example like this:

select t1.*, c.user as `last_user`, c.datetime as `last_date` from topics as t1 inner join (select id_topic, max(id) max_id from comments c1 group by id_topic) t2 on (t2.id_topic = t1.id) inner join comments c on (c.id = t2.max_id);

Using ORDER BY to find the maximum element is not a good idea (although it seems that the mysql optimizer, when using ORDER BY and LIMIT, understands that it is not necessary to sort)

Answer 2

In theory it can be done like this:

SELECT t1.id,
    t1.title,
    t2.user AS `last_user`,
    t2.datetime AS `last_date` 
FROM `comments` t2, topics t1  
WHERE t1.id=t2.id_topic 
ORDER BY t2.id DESC 
LIMIT 1