0

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

3
  • Which is "this version of MariaDB"? – mustaccio Jun 3 '20 at 15:27
  • ROW_NUMBER in CTE. – Akina Jun 3 '20 at 15:31
  • @mustaccio I use the following MariaDB Docker image: mariadb:10.5.3. @Akina I have never used these options before. I will look for them. Thank you all. – rmmariano Jun 3 '20 at 15:33
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
1
  • Sir, your query worked. Thank you so much. – rmmariano Jun 3 '20 at 18:14
0
 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

1
  • Thank you for your contribution. Please, could you provide a short explanation of the query? I tried to execute your query, but MariaDB returned the following error message: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'HAVING counter < 3' at line 6 – rmmariano Jun 3 '20 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.