Mysql limit with in statement

Question

I am writing a query

SELECT 
   `user_bookmarks`.`id` as `user_bookmark_id`,
   `bookmark_id`,
   `user_bookmarks`.`user_id`,
   `bookmark_url`,
   `bookmark_website`,
   `bookmark_title`,
   `bookmark_preview_image`,
   `bookmark_popularity`,`category_id`,
   `category_name`,
   `pdf_txt_flag`,
   `youtube_video`,
   `content_preview`,
   `snapshot_preview_image`,
   `mode` 
FROM 
   `user_bookmarks` 
   left join `bookmarks` 
      on `user_bookmarks`.`bookmark_id` = `bookmarks`.`id` 
   left join `categories` 
      on `user_bookmarks`.`category_id` = `categories`.`id` 
   WHERE 
      `category_id` IN(164,170,172) 
   LIMIT 0, 6

it is fetching first 6 bookmarks. But i want it will fetch 6 bookmarks from each category. Can we do this in mysql itself.

Please adivce..

Thanks

score 1 · Accepted Answer · answered Aug 04 '14 at 06:21

1

You can use a rank query to give a rank for each category and filter it with your desired no of records per category below query will give you 6 records from each category

SELECT t.* FROM
(SELECT 
  `user_bookmarks`.`id` AS `user_bookmark_id`,
  `bookmark_id`,
  `user_bookmarks`.`user_id`,
  `bookmark_url`,
  `bookmark_website`,
  `bookmark_title`,
  `bookmark_preview_image`,
  `bookmark_popularity`,
  `category_id`,
  `category_name`,
  `pdf_txt_flag`,
  `youtube_video`,
  `content_preview`,
  `snapshot_preview_image`,
  `mode` ,
   @r:= CASE WHEN category_id = @g THEN @r+1  ELSE @r:=1 END `rank` ,
   @g:=category_id
FROM
  `user_bookmarks` 
  LEFT JOIN `bookmarks` 
    ON `user_bookmarks`.`bookmark_id` = `bookmarks`.`id` 
  LEFT JOIN `categories` 
    ON `user_bookmarks`.`category_id` = `categories`.`id` 
  JOIN (SELECT @r:=0,@g:=0) t1
WHERE `category_id` IN (164, 170, 172) 
ORDER BY category_id
) t
WHERE t.rank <=6

answered Aug 04 '14 at 06:21

M Khalid Junaid

63,861
10
90
118

Can you please tell me what is t.rank<=6 . i didnt get – Hitu Aug 04 '14 at 06:24
i have calculate a rank for records of each category id so records from category 164 will have a rank starting from 1,2,3,4,5,.... and so on for each category in outer query i have just filtered records to get 6 records for each category by doing `WHERE t.rank <=6` – M Khalid Junaid Aug 04 '14 at 06:32
But my limit is coming dynamic for each request.. i want to add read more for each article.. then i dnt think so rank will work – Hitu Aug 04 '14 at 07:04
You can alter your where for dynamic limit by doing `WHERE t.rank >=1 AND t.rank <= 6` for next request you can use it like `WHERE t.rank >=7 AND t.rank <= 12` and so on for upcoming requests – M Khalid Junaid Aug 04 '14 at 08:52

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

You have to use the GROUP BY statement, but you cannot have a limit per each one of them.

I suggest you look at this question with answer in Old post and read the article mentioned in the post.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 04 '14 at 06:16

Gabriel C. Troia

3,180
2
16
17

Mysql limit with in statement

2 Answers2

Linked