MariaDB – Fixing ‘#1235 – This Version Doesn’t Support LIMIT & IN/ALL/ANY/SOME Subquery’

mariadbMySQL

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

Best Answer

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
Related Question