0

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.

Improve this question
0

1 Answer 1

Reset to default
4

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

thank you but your code works but result is not I want, your result has story_id ASC (from 1), other cols are random
Check the updated answer.
Adding an index on (story_id, chapter_id) should help both of the queries in my answer.
@PhongThai that's the version of your command line client, not of your database server. Btw, the command line client is for mariadb, not for mysql. Be careful with mysql solutions, not all of them work on mariadb!
@Shadow ...and vice-versa as well, e.g. if using ROW_NUMBER() on MariaDB but still using MySQL 5.7.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.