Retriving 3 last records from database for each id

[marklenon95]

Hello,

I want to retrive last three posts from database based on category id, e.g

table

post_id         category_id
   1                 1
   2                 4
   3                 3
   4                 4
   5                 2
   6                 1
   7                 3
   8                 2
   9                 4
  10                 1
  11                 2
  12                 3

and I want to retrive something like this

post_id         category_id
   1                 1
   6                 1
  10                 1
   5                 2
   8                 2
  11                 2
   7                 3
   3                 3
  12                 3
   4                 4
   2                 4
   9                 4

I have made this query but how to limit to return 3 of each category_id?

SELECT
            `posts`.`title`,
            `posts`.`slug`,
            `posts`.`image`,
            `posts`.`status`,
            `category`.`name` AS `cName`
          FROM 
            `posts`
          INNER JOIN 
            `category`
          ON
            `posts`.`category` = `category`.`id`
          ORDER BY
            `category`.`name`

How to limit to return 3 records for each category_id, when I use LIMIT 3 query returns only 3 records.

[WebMachine]

This question seems to be more about database queries, so I’m going to move it to the databases category. You might get more response there.

[r937]

SELECT p.title , p.slug , p.image , p.status , c.name AS cName FROM category AS c INNER JOIN posts AS p ON p.category = c.id AND ( SELECT COUNT(*) FROM posts WHERE category = p.category AND id > p.id ) < 3 ORDER BY c.name

[marklenon95]

Thank you very much sir!