How to select top N record from every group MySql

Question

I have a table where i want to get top N items from each group on the base of column sub_category_id. If i set N=10 that would select first 10 rows from each group or if i set N=5 then that would select top `5 rows from each group. Please help me.

score 4 · Accepted Answer · answered Feb 03 '18 at 03:09

4

There are multiple ways to do this. Here is one using a correlated subquery:

select t.*
from t
where (select count(*)
       from t t2
       where t2.sub_category_id = t.sub_category_id and t2.id <= t.id
      ) <= 10;

answered Feb 03 '18 at 03:09

Gordon Linoff

1,242,037
58
646
786

`select sub_category_items.* from sub_category_items where (select count(*) from sub_category_items t2 where t2.sub_category_id = sub_category_items.sub_category_id and t2.id <= sub_category_items.id ) <= 10` – irjawais Feb 03 '18 at 03:20
This works, but I think the solution here https://stackoverflow.com/a/30269273 with a join instead of a subquery probably performs better - I tried both variants, and they yield the same (correct) result, but the one with the join was faster than the one with the subquery. – leo Mar 14 '20 at 15:02
@leo . . . That is actually irrelevant, because MySQL supports window functions now. – Gordon Linoff Mar 14 '20 at 19:59
@GordonLinoff okay thanks but I'm stuck with an old version (5.x) of MySQL – leo Mar 16 '20 at 02:49

score 0 · Answer 2 · answered Jul 04 '23 at 11:15

Use ROW_NUMBER() and PARTITION BY as below.

In my case, the table name was company_perform_quter. I used group by single column, order by multiple column and received latest four record.

SELECT* from (SELECT *, ROW_NUMBER() OVER( PARTITION BY symbol ORDER BY symbol, year DESC, month DESC, quater DESC) rowNum
FROM company_perform_quter) nn WHERE rowNum<=4

How to select top N record from every group MySql

2 Answers2

Linked