Get 2 recent posts for each category

Question

Let's say I have 2 tables: blog_posts and categories. Each blog post belongs to only one category, so there is a foreign key between the two tables.

I would like to receive the last 2 posts from each category, is it possible to do this in one request? GROUP BY will group everything and leave me only one row in each category. But I want 2 of them.

It would be easy to execute a query 1 + N (N = number of categories). First select the categories. And then get 2 posts from each category.

I believe that it would be very easy to execute M queries (M = the number of posts I want from each category). The first query selects the first message for each category (with group). The second request receives a second message for each category. and etc.

I'm just wondering if anyone has a better solution for this. I really do not mind making 1 + N queries for this, but for curiosity and general knowledge of SQL, it would be appreciated!

Thanks in advance who can help me.

Answer 1

Refuse the MySQL article on how to work with the vertices of N things in arbitrarily complex groups; this is good stuff. You can try the following:

SET @counter = 0;
SET @category = '';

SELECT
  *
FROM
(
  SELECT
    @counter := IF(posts.category = @category, @counter + 1, 0) AS counter,
    @category := posts.category,
    posts.*
    FROM
      (
      SELECT
        *
        FROM test
        ORDER BY category, date DESC
      ) posts
) posts
HAVING counter < 2

Answer 2

SELECT  p.*
FROM    (
        SELECT  id,
                COALESCE(
                (
                SELECT  datetime
                FROM    posts pi
                WHERE   pi.category = c.id
                ORDER BY
                        pi.category DESC, pi.datetime DESC, pi.id DESC
                LIMIT 1, 1
                ), '1900-01-01') AS post_datetime,
                COALESCE(
                (
                SELECT  id
                FROM    posts pi
                WHERE   pi.category = c.id
                ORDER BY
                        pi.category DESC, pi.datetime DESC, pi.id DESC
                LIMIT 1, 1
                ), 0) AS post_id
        FROM    category c
        ) q
JOIN    posts p
ON      p.category <= q.id
        AND p.category >= q.id
        AND p.datetime >= q.post_datetime
        AND (p.datetime, p.id) >= (q.post_datetime, q.post_id)

posts (category, datetime, id), .

p.category <= c.id AND p.category >= c.id hack: MySQL Range checked for each record, .

. :

• MySQL: ROW_NUMBER ORDER BY