MySQL select top N rows for each category

Question

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

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;

Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Gonzalo.-, Feb 24 '17 at 17:41

score 5 · Answer 1 · answered Feb 24 '17 at 17:43

5

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;

answered Feb 24 '17 at 17:43

Gordon Linoff

1,242,037
58
646
786

I've edited my question above and it worked, the answer you have is kinda similar to mine as well. thanks for the answer. – Ibraheem Al-Saady Feb 24 '17 at 17:52
Your answer is not guaranteed to work, because you set variables and then use them in different expressions. MySQL does not guarantee the order of evaluation of expressions in the `select`. – Gordon Linoff Feb 25 '17 at 04:45

score -2 · Answer 2 · answered Feb 24 '17 at 17:47

-2

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.

answered Feb 24 '17 at 17:47

magicleon94

4,887
2
24
53

that will only get you the first 2 rows from the whole result – Ibraheem Al-Saady Feb 25 '17 at 10:13
sorted by `created_at`, so it will return the top 2 items – magicleon94 Feb 25 '17 at 10:15
yeah exactly, what I want is to get top 2 from **each** category I have sorted by created_at. so if I have multiple categories, your query will get me the top 2 from the whole result regardless of category sorted by created_at of course. – Ibraheem Al-Saady Feb 25 '17 at 10:20
i remembered that this would limit the result of each "group", my mistake – magicleon94 Feb 25 '17 at 10:22

MySQL select top N rows for each category

2 Answers2