SQL ON SINGLE TABLE GROUP BY LIMIT 10

I have a table with items (id_item, name, category, stock,…) I’d like make a query to group by result on category and LIMIT 10 first items which are in this category

Is it possible?

Answer

You can use row_number():

select i.*
from (select i.*,
             row_number() over (partition by category order by ?) as seqnum
      from items i
     ) i
where seqnum <= 10;

The ? is for the column that specifies what YOU mean by “first”.