5 latest topics with only 1 reply

Question

There are 2 tables, one for topics, the second for answers.
How to use 1 SQL query to display 5 extreme topics, with only 1 extreme answer.
In this case, new answers should raise the topic higher in the list, as it works with the use of DESC.

I tried to do this, but it doesn't work quite correctly:

SELECT `t2`.`date` AS `date`,`t1`.`name` AS `name` 
FROM `quests` 
AS `t1` 
LEFT JOIN `answers` 
AS `t2` 
ON `t2`.`quest_id` = `t1`.`id` 
GROUP BY `t1`.`id` DESC

How to do this correctly?

Table structures are quite simple:
quests:

+----+----------------+--------------------------------+------------+
| id | name           | text                           | date       |
+----+----------------+--------------------------------+------------+
|  1 | Quest 1        | Quest 1 Text                   | 1702570100 |
+----+----------------+--------------------------------+------------+
|  2 | Quest 2        | Quest 2 Text                   | 1702570120 |
+----+----------------+--------------------------------+------------+

answers:

+----+-----------+-----------------------------+------------+
| id | quest_id  | text                        | date       |
+----+-----------+-----------------------------+------------+
|  1 |         1 | Answer 1                    | 1702570135 |
+----+-----------+-----------------------------+------------+
|  2 |         1 | Answer 2                    | 1702570136 |
+----+-----------+-----------------------------+------------+
|  3 |         2 | Answer 1                    | 1702570137 |
+----+-----------+-----------------------------+------------+
|  4 |         2 | Answer 2                    | 1702570138 |
+----+-----------+-----------------------------+------------+
|  5 |         2 | Answer 3                    | 1702570139 |
+----+-----------+-----------------------------+------------+

As a result, it should look like this:

Quest 2
- Answer 3
Quest 1 
- Answer 2

I also tried doing this:

SELECT `t2`.`date` AS `date`,`t1`.`name` AS `name` 
FROM `quests` 
AS `t1` 
JOIN (SELECT * FROM `answers` ORDER BY `date` DESC) 
AS `t2` 
ON `t2`.`quest_id` = `t1`.`id` GROUP BY `t1`.`id` DESC LIMIT 5

Nothing comes of it.

Answer 1

You will need to find the latest answer per quest_id from table answers :

SELECT quest_id, MAX(date) AS latest_date
FROM answers
GROUP BY quest_id

Then join this dataset to tables answers and quests to get the expected output :

SELECT q.name AS Question, a.text AS answer
FROM quests AS q
INNER JOIN answers a ON a.quest_id = q.id
INNER JOIN (
  SELECT quest_id, MAX(date) AS latest_date
  FROM answers
  GROUP BY quest_id
) AS s on s.quest_id = a.quest_id and a.`date` = s.latest_date
ORDER BY latest_date DESC
LIMIT 5;

Results :

Question    |answer
------------|----------
Quest 2     |Answer 3
Quest 1     |Answer 2

Demo here

---

Use LEFT JOIN instead of INNER JOIN if there are questions don't have answers that also need to be listed :

SELECT q.name AS Question, a.text AS answer
FROM quests AS q
LEFT JOIN answers a ON a.quest_id = q.id
LEFT JOIN (
  SELECT quest_id, MAX(date) AS latest_date
  FROM answers
  GROUP BY quest_id
) AS s on s.quest_id = a.quest_id and a.`date` = s.latest_date
WHERE s.quest_id IS NOT NULL OR a.id IS NULL
ORDER BY latest_date DESC
LIMIT 5;

Which for this data :

create table quests (
  id int,
  name varchar(20),
  `text` varchar(20),
  `date` int
);

insert into quests values
(1, 'Quest 1', 'Quest 1 Text', 1702570100),
(2, 'Quest 2', 'Quest 2 Text', 1702570120 ),
(3, 'Quest 3', 'Quest 2 Text', 1702570120 );

create table answers (
  id int,
  quest_id  int,
  `text` varchar(20),
  `date` int
);

insert into answers values
(1, 1, 'Answer 1', 1702570135),
(2, 1, 'Answer 2', 1702570136),
(3, 2, 'Answer 1', 1702570137),
(4, 2, 'Answer 2', 1702570138),
(5, 2, 'Answer 3', 1702570139);

Results :

Question    |answer
------------|-------
Quest 3     |null
Quest 2     |Answer 3
Quest 1     |Answer 2

Demo here