CODEWITHSUNDEEP
mysql
Ibraheem Al-Saady
Ibraheem Al-Saady

Reputation: 874

MySQL select top N rows for each category

I'm trying to get top 2 latest rows from each category, and this can be scaled later on so I might for example get top 4 rows instead of top 2.

here is how my table looks like

  Id   | category_id | created_at
------ | -----------   ----------
   1   |      1      | 2017-12-01
   2   |      2      | 2017-12-02
   3   |      4      | 2017-12-03
   4   |      2      | 2017-12-04
   5   |      1      | 2017-12-05
   6   |      1      | 2017-12-06
   7   |      3      | 2017-12-07
   8   |      4      | 2017-12-08
   9   |      4      | 2017-12-09
   10  |      3      | 2017-12-10
   11  |      5      | 2017-12-11

I want to get the latest 2 rows (based on the created_at column) ids so I want the output to be something like

      Ids     
    ------ 
       5   
       6   
       2   
       4   
       7
       10
       8
       9
       11

I'm doing something like 

select * from table
inner join (
    select * from table
    order by category_id, created_at
    limit 2
) as tb
on tb.id = table.id and tb.category_id = table.category_id

Obviously it's not working, just wanted to share what I reached so far. Is there a way to do that in MySQL?

EDIT

I actually did this and it kinda worked

SELECT *
   FROM
     (SELECT *, 
                  @count := IF(@current_category = category_id, @count + 1, 1) AS count,
                  @current_category := category_id 
       FROM table
       ORDER BY category_id, created_at DESC
     ) ranked
   WHERE count <= 2;

Upvotes: 1

Views: 4861

Answers (2)

magicleon94
magicleon94

Reputation: 5162

I would go with something like this: 

select * from table group by category_id order by created_at limit 2

My mySql skills are a bit rusty but this (or the concept of it) should do the trick.

Upvotes: -2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

This is more of a pain in MySQL than it should be. Probably the easiest way is to use variables:

select t.*
from (select t.*,
             (@rn := if(@c = category_id, @rn + 1,
                        if(@c := category_id, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @rn := 0, @c := -1) params
      order by category_id, created desc
     ) t
having rn <= 2;

Upvotes: 5

Related Questions