Show the first X results in Group Concat

I have a query that returns a list of items that an owner has:

WITH all_items
AS
  (
         SELECT owner.id AS owner,
                item.title,
                item.importance
         FROM   owner
         JOIN   item
         ON     item.owner_id = owner.id)
  SELECT   owner.id,
           group_concat(DISTINCT item.title ORDER BY item.importance SEPARATOR ',')
  FROM     owner
  JOIN     all_items
  ON       all_items.owner = owner.id

I need to limit the group_concat to max of 6 items. I can't do LIMIT in all_items because it returns only the top 6 items, regardless of who the owner is. How do I do this?

Solution

The simplest method is substring_index():

substring_index(group_concat(DISTINCT item.title ORDER BY item.importance SEPARATOR ','), ',', 6)

Your query is missing a GROUP BY and seems overly complicated. I have no idea why you are joining back to the owner table again. In fact, you don't need to join to it at all. Why not just use this?

SELECT i.owner_id AS owner,
       group_concat(DISTINCT i.title ORDER BY i.importance SEPARATOR ',')
FROM item i
GROUP BY i.owner_id;

You can get the first six using a different method as well:

SELECT i.owner_id AS owner,
       group_concat(DISTINCT i.title ORDER BY i.importance SEPARATOR ',')
FROM (SELECT i.*,
             DENSE_RANK() OVER (PARTITION BY i.owner_id ORDER BY i.importance DESC, i.title) as seqnum
      FROM item i
     ) i
WHERE seqnum <= 6
GROUP BY i.owner_id;

This has the advantage that you don't have to worry about GROUP_CONCAT() string limits (assuming the titles are not very long) when an owner owns zillions of items.