Query and get up to maximum items per group

SQL++
1

Given the following object
{
id: string,
kind1: string,
kind2: string
}
How can i do the following query:
Give me all ids, grouped by (kind1, kind2), limit up to 5 objects per group. thanks

2 
SELECT d1.kind1, d1.kind2, (SELECT  RAW d2 FROM d1.ids AS d2 LIMIT 5) AS ids
FROM ( SELECT  d.kind1, d.kind2, ARRAY_AGG(d.id) AS ids
         FROM defualt AS d 
         GROUP BY d.kind1, d.kind2) AS d1;
3

Thank you.
If i understand correctly, ARRAY_AGG(d.id) will fetch ALL the items, hence i won’t benefit in performance from the “LIMIT 5”. is that correct? is there a way to make it efficient?

4

There is no other way you can tell aggregate stop in the middle.

Other option is two queries.

SELECT DISTINCT d.kind1, d.kind2
FROM default AS d
WHERE …;

For each Value

   SELECT d.id, d.kind1, d.kind2 FROM default
   WHERE d.kind1 = $kind1 AND d.kind2 = $kind2
   LIMIT 5;
5

What i’ve ended up doing. Not very elegant (once you get many different values of kind1, kind2) but the only way i figured…