MySQL Select MAX() & Group By do not work as I want

I have 2 tables with 50k+ & 10k+ rows

Table chapter

id (auto_inc) | chapter_id | story_id | date (timestamp)
1             | 1          | 1        | 1715830560
...
10            | 10         | 1        | 1715830570 <- latest chapter of story #1

100           | 1          | 2        | 1715830560
...
111           | 11         | 2        | 1715830570 <- latest chapter of story #2

200           | 1          | 3        | 1715830560
...
211           | 21         | 3        | 1715830570 <- latest chapter of story #3

Table story

id (auto_inc) | slug        | title     | date (timestamp)
1             | slug-1      | title 1   | 1715830560
...
100           | slug-100    | tit 100   | 1715830580 <- consider this is latest story

I used this command below to get the latest unique chapter of each story

SELECT C.id, MAX(C.chapter_id), C.story_id, C.title AS title_chapter, C.summary
FROM `chapter` C
GROUP by C.story_id
ORDER by C.id DESC
LiMIT 10;

It shows successfully the 10 unique stories with their chapters, but it always picks chapters by the latest story, mean it always pick the latest stories first then pick the latest chapters for each story; result like below:

id            | chapter_id | story_id (↓) | date (timestamp)
205           | 5          | 100          | 1715830560 <- 205 is latest chapter of story #100
108           | 8          | 99           | 1715830560 <- ..
8             | 8          | 98           | 1715830560 <- ..

But I want latest chapter sorted by id, mean the latest chapter of stories, not latest stories with chapter, result like below:

id (↓)        | chapter_id (latest of each story) | story_id (unique) | date (timestamp)
508           | 15                                | 75                | 10
507           | 35                                | 95                | 9 
506           | 5                                 | 15                | 8 
505           | 15                                | 100               | 8 
504           | 35                                | 99                | 8

And please suggest me the solution (best performance with minium processing time) to get title & slug from story table for these latest chapters.

Many thanks for advising.

Solution

Your current query is invalid, because it aggregates by the story_id, but then selects non aggregate columns. Your code would not even run on MySQL with the ONLY_FULL_GROUP_BY flag enabled. A canonical approach to your problem would use the ROW_NUMBER() window function:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY story_id ORDER BY chapter_id DESC) rn
    FROM chapter
)

SELECT id, chapter_id, story_id, title AS title_chapter, summary
FROM cte
WHERE rn = 1
ORDER BY date DESC
LIMIT 10;

For MySQL 5.7 or earlier:

SELECT c1.id,  c1.chapter_id,  c1.story_id,  c1.title AS title_chapter,  c1.summary
FROM chapter c1
INNER JOIN (
    SELECT story_id, MAX(chapter_id) AS max_chapter_id
    FROM chapter
    GROUP BY story_id
) c2
    ON c2.story_id = c1.story_id AND
       c2.max_chapter_id = c1.chapter_id
ORDER BY c1.date DESC
LIMIT 10;