2

I have a query similar like this. 

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

it's refer to this answer. This is link

Can we count total number of result in each Category_id in above query. Can we do it in mysql itself.

Thank you.

Community
  • 1
  • 1
Mohit Kumar
  • 87
  • 2
  • 9

1 Answers1

2

You can do this with a bit of a trick:

select t.*
from (select t.*,
              @r := (CASE WHEN category_id = @g THEN @r ELSE rank END) as maxrank,
              @g := category_id
      from (<inner query here>) t
      order by category_id, t.rank desc
     ) t
where t.rank <= 6;

The idea is to re-sort the data descending and then copy the rank across the rows for each category.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786