CODEWITHSUNDEEP
mysql
Hitu
Hitu

Reputation: 286

Mysql limit with in statement

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

Upvotes: 2

Views: 71

Answers (2)

Gabriel C. Troia
Gabriel C. Troia

Reputation: 3330

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.

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions