#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Question

I have a table with several "categories", called collection (e.g. A, B, C, ...).

I would like to create a SQL query to select N records by each collection.

For example:

I would like to select 2 records by each collection.

Table stac_item:

id | collection | name
1  | A          | name_01
2  | A          | name_02
3  | A          | name_03
4  | B          | name_04
5  | B          | name_05
6  | B          | name_06
7  | B          | name_07
8  | C          | name_08
9  | C          | name_09

I expect the following result:

id | collection | name
1  | A          | name_01
2  | A          | name_02
4  | B          | name_04
5  | B          | name_05
8  | C          | name_08
9  | C          | name_09

I have created the SQL query below, but MariaDB gives the following error message:

#1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

SQL query:

SELECT * 
FROM stac_item a1
WHERE a1.collection IN
(
    SELECT a2.collection 
    FROM stac_item a2 
    WHERE a1.collection = a2.collection 
    LIMIT 2
)

Would anyone know an alternative that I could use? I have searched on the internet, but I was not able to find a solution.

Thank you.

EDIT 1:

I use the following MariaDB Docker image: mariadb:10.5.3

Answer 1

You could use the window function row_number, like so:

select id, collection, name from (
  select id, collection, name, 
  row_number() over (partition by collection order by id) rn
  from stac_item 
) t
where rn < 3
order by id

Answer 2

 SELECT w.* 
      , IF(@coll = @coll:=w.collection, @cnt:=@cnt+1, @cnt:=1) AS counter
   FROM stac_item AS w
  WHERE @cnt := 1
  ORDER BY w.collection ASC, w.name ASC
 HAVING counter < 3

This query can be very slow on a huge tables