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.
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
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