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
I would like to select 2 records by each
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'
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.
I use the following MariaDB Docker image: