How to select multiple items from each group in mysql query?

Question

I have some data in the form forum

post (author, thread_id, text)

For each author, I would like to select 10 different thread threads associated with this author (there may be more than 10, and the number will vary depending on the author).

I am going to use GROUP BY to group on "author", but I cannot figure out how to express LIMIT for each group, and how to expand each group by 10 lines.

Answer 1

Here is a solution for queries like “top N per group”.

Please note that you need to choose which 10 threads for this author you want. In this example, I assume that you need the most recent threads (and thread_id is the auto-increment value), and for link cases you have the primary key posts.post_id .

 SELECT p1.* FROM post p1 LEFT OUTER JOIN post p2 ON (p1.author = p2.author AND (p1.thread_id < p2.thread_id OR p1.thread_id = p2.thread_id AND p1.post_id < p2.post_id)) GROUP BY p1.author HAVING COUNT(*) < 10; 

---

Repeat your next question in the comment, here is the explanation:

In the top 10 threads for the author, you can say that for each of them there are 9 or fewer other threads for this author, which belongs to the result set. So, for each author’s message (p1), we calculate how many messages (p2) from the same author have a larger stream. If this number is less than 10, then as a result this post of the author (p1) will be published.

I have added a term to allow links to post_id.