2

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?

bella lugosi
  • 49
  • 7

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hey! Thank you so much for your answer, the first part helped me big time, I didn't remember that, it's super smart! The reason it's so complicated is that it's just a part of a bigger thing and I simplified it so I could easily explain what is it exactly that I need. TL;DR, the main owner is parametrized and gets data from over 20 tables, so I chunked them in order to be able to make future additions/fixes without disrupting what already is in motion. Thanks again! <3 – bella lugosi Sep 03 '21 at 11:35
  • By the way, RANK is completely alien to me, but I see it mentioned around a lot, I'll definitely get to read more about it this weekend, seems super useful! – bella lugosi Sep 03 '21 at 11:39