Fetch maximum of x rows per specific row...

[Mr. Goodie2Shoes]

Yeah, the title is quite vague. So here's an example:
There are one post for thread 1 (that's the ID), ten posts for thread 2, two posts for thread 3

Now I want to fetch a max of 2 posts per thread.
And the result would return:

• 1 post from thread 1
• the most recent 2 posts from thread 2
• the most recent 2 posts from thread 3

Though I am not sure if that's remotely possible with a single query...

Thanks!

 

[Paul B]

Off the top of my head, can't you select the posts in post date, DESC order, limit 2?

 

[Liam W]

Ah, you would probably have to join the posts table and sort by post date...

Liam

 

[Mr. Goodie2Shoes]

With a single query: that would return the last 2 posts from all three threads.
And for what I want I would either have to use "UNION" or three separate queries to fetch the last 2 posts from each thread...

 

[Chris D]

Obviously we don't know the bigger picture, but often there's no harm in using multiple queries for something like this.

You'd have to consider how scalable something like this would be with hundreds of thousands of threads and millions of posts and whether or not multiple queries would actually be ultimately better for performance than a single query. Think about whether the complicated query can use available indexes. If it can't, it probably won't scale well.

Also consider whether this is something that can be cached, rather than needing multiple queries or complicated queries.

 

[Mr. Goodie2Shoes]

Yeah... maybe it's not the effort so I am going with something like fetch the first latest post, then the second latest post from all the threads instead of fetching the first two posts per thread. So the query count is limited to 2
I'll add some kind of cache on a later version I guess.