Retriving 3 last records from database for each id

Question

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

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`.`id`,
     `posts`.`title`,
     `posts`.`slug`,
     `posts`.`image`,
     `posts`.`status`
FROM 
     `posts`
ORDER BY
     `posts`.`id`

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

See for instance https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results or any of the hundreds of other similar questions. — Strawberry, Jun 11 '17 at 10:51

Santosh · Accepted Answer · 2017-06-11T12:25:44.280

1

You can read 3 new post_id from each category_id

SELECT
    p1.`post_id`,
    p1.`category_id`
FROM
    post p1
JOIN post p2 ON p1.`category_id` = p2.`category_id`
AND p2.`post_id` >= p1.`post_id`
GROUP BY
    p1.`post_id`,
    p1.`category_id`
HAVING
    COUNT(*) <= 3
ORDER BY
    `category_id`,
    `post_id`

SQL FIDDLE : LIVE DE

edited Jun 11 '17 at 12:25

answered Jun 11 '17 at 12:15

Santosh

393
2
11

score -2 · Answer 2 · answered Jun 11 '17 at 10:56

-2

$sql = "SELECT `posts`.* FROM `posts` WHERE `posts`.`category_id` = 1 ORDER BY `posts`.`id` DESC LIMIT 3"

run it then

$sql = "SELECT `posts`.* FROM `posts` WHERE `posts`.`category_id` = 2 ORDER BY `posts`.`id` DESC LIMIT 3"

run that then

$sql = "SELECT `posts`.* FROM `posts` WHERE `posts`.`category_id` = 3 ORDER BY `posts`.`id` DESC LIMIT 3"

then that, but don't forget

$sql = "SELECT `posts`.* FROM `posts` WHERE `posts`.`category_id` = 4 ORDER BY `posts`.`id` DESC LIMIT 3"`

then you have reached your destination

it is better to just run the queries separate.

answered Jun 11 '17 at 10:56

Amplifier

143
1
1
11

Can you explain why is better to run separate queries then runing all in one query? Regards! – Aleksa Arsić Jun 11 '17 at 10:59
Lol there is more than one way to reach your destination as long as the script gets job done correctly. Stop thinking like a robot and gt with humanity. – Amplifier Jun 11 '17 at 14:00

Retriving 3 last records from database for each id

2 Answers2