# How to return top-N from each group with GROUP_CONCAT()

A practical example of how to select several most recent items from each group in Manticore Search and combine them into a single string.

Imagine a customer support screen. An agent searches for refund-related events and, instead of a long log, wants a short summary: one row per user, the total number of matches, and the **five** most recent events. If more details are needed, the application can load them by ID.

Two obvious approaches don't quite give us what we need:
- regular `GROUP_CONCAT()` collects all values in the group together,
- while `GROUP N BY` returns several rows per user.

Starting with [Manticore Search 28.6.6](/blog/manticore-search-28-6-6/), values inside `GROUP_CONCAT()` can be sorted and limited to the number you need:

```sql
GROUP_CONCAT(id ORDER BY event_ts DESC, id DESC LIMIT 5)
```

Let's see how it works. We'll use SphinxQL and an explicit `GROUP BY` - the new form of `GROUP_CONCAT()` doesn't work without it.

## Events we'll work with

Let's create an `activity` table where each document is a single event. The event text is stored in `body`, the user in `user_id`, and the timestamp in `event_ts`.

```sql
CREATE TABLE activity (
    body text,
    user_id int,
    event_ts bigint,
    event_type string
);

INSERT INTO activity (id, body, user_id, event_ts, event_type) VALUES
    (1001, 'refund requested for order 501',       101, 1770000010, 'requested'),
    (1002, 'user logged in',                       101, 1770000020, 'login'),
    (1003, 'refund approved for order 501',        101, 1770000030, 'approved'),
    (1004, 'refund email sent for order 501',      101, 1770000040, 'email'),
    (1005, 'refund status checked for order 501',  101, 1770000050, 'checked'),
    (1006, 'refund payout queued for order 501',   101, 1770000060, 'queued'),
    (1007, 'refund webhook retried for order 501', 101, 1770000060, 'retried'),
    (2001, 'refund requested for order 601',       202, 1770000015, 'requested'),
    (2002, 'refund approved for order 601',        202, 1770000025, 'approved'),
    (2003, 'shipping address changed',             202, 1770000035, 'shipping'),
    (2004, 'refund payout queued for order 601',   202, 1770000045, 'queued'),
    (2005, 'refund completed for order 601',       202, 1770000055, 'completed'),
    (3001, 'refund requested for order 701',       303, 1770000012, 'requested'),
    (3002, 'invoice downloaded',                   303, 1770000022, 'invoice'),
    (3003, 'refund rejected for order 701',        303, 1770000032, 'rejected');
```

A search for `refund` will find six events for user 101, four for user 202, and two for user 303. We deliberately gave events 1006 and 1007 the same timestamp: later you will see why sorting also needs `id`.

## What's wrong with the old approaches

Let's start with regular `GROUP_CONCAT()`. The response format is what we need - one row per user:

```sql
SELECT
    user_id,
    COUNT(*) AS matched_events,
    GROUP_CONCAT(id) AS all_event_ids
FROM activity
WHERE MATCH('refund')
GROUP BY user_id
ORDER BY matched_events DESC, user_id ASC;
```

But for user 101, the string will contain all six IDs, for example `1001,1003,1004,1005,1006,1007`, while we only need the five most recent ones. Also, without internal sorting, the order of the values is not guaranteed.
```text
+---------+----------------+-------------------------------+
| user_id | matched_events | all_event_ids                 |
+---------+----------------+-------------------------------+
|     101 |              6 | 1001,1003,1004,1005,1006,1007 |
|     202 |              4 | 2001,2002,2004,2005           |
|     303 |              2 | 3001,3003                     |
+---------+----------------+-------------------------------+
```

Another option is to ask `GROUP N BY` to select the five most recent documents from each group:

```sql
SELECT
    id,
    user_id,
    event_ts
FROM activity
WHERE MATCH('refund')
GROUP 5 BY user_id
WITHIN GROUP ORDER BY event_ts DESC, id DESC
ORDER BY user_id ASC;
```

The oldest event for user 101 disappears, but each remaining document is returned as a separate row. So instead of three rows, we get eleven: 5 + 4 + 2. This is convenient when the client needs the documents themselves, but it doesn't work for our compact summary.
```text
+------+---------+------------+
| id   | user_id | event_ts   |
+------+---------+------------+
| 1007 |     101 | 1770000060 |
| 1006 |     101 | 1770000060 |
| 1005 |     101 | 1770000050 |
| 1004 |     101 | 1770000040 |
| 1003 |     101 | 1770000030 |
| 2005 |     202 | 1770000055 |
| 2004 |     202 | 1770000045 |
| 2002 |     202 | 1770000025 |
| 2001 |     202 | 1770000015 |
| 3003 |     303 | 1770000032 |
| 3001 |     303 | 1770000012 |
+------+---------+------------+
```

## Combining only the five most recent IDs

Now let's combine both steps: sort the documents directly inside `GROUP_CONCAT()` and limit the list to five values there as well.

```sql
SELECT
    user_id,
    COUNT(*) AS matched_events,
    GROUP_CONCAT(
        id
        ORDER BY event_ts DESC, id DESC
        LIMIT 5
    ) AS recent_event_ids
FROM activity
WHERE MATCH('refund')
GROUP BY user_id
ORDER BY matched_events DESC, user_id ASC;
```

```text
+---------+----------------+--------------------------+
| user_id | matched_events | recent_event_ids         |
+---------+----------------+--------------------------+
|     101 |              6 | 1007,1006,1005,1004,1003 |
|     202 |              4 | 2005,2004,2002,2001      |
|     303 |              2 | 3003,3001                |
+---------+----------------+--------------------------+
```

First, `MATCH('refund')` selects refund events, then `GROUP BY user_id` groups them by user. `COUNT(*)` counts all matching events, while `GROUP_CONCAT()` takes only the first five from each group after sorting.

The second sort key - `id DESC` - is especially important here. Events 1006 and 1007 have the same `event_ts`, so without it their relative order would be undefined. Sorting by ID ensures that event 1007 always comes first.

Notice that the query has two `ORDER BY` clauses. The one inside `GROUP_CONCAT()` determines the order of IDs in the string. The final `ORDER BY` sorts the completed rows: first by the number of matches, then by `user_id`.

The inner `LIMIT` does not change `COUNT(*)` or affect pagination of the overall result. So user 101 still has six matches even though only five IDs are shown next to it. That's exactly what we need for this summary.

One more detail: `GROUP_CONCAT()` always returns a string, even when it contains numeric IDs. If the API needs to return an array of numbers or objects with several fields, the string has to be parsed on the client side, or a different response format should be used.

The query works the same way with distributed tables. Manticore collects candidates from all local and remote tables, then selects the overall top-N for each group.

## If a comma doesn't work

By default, values are separated by commas. Sometimes a more readable string is useful - for example, showing the event type next to its ID. You can set a custom separator with `SEPARATOR`:

```sql
SELECT
    user_id,
    GROUP_CONCAT(
        CONCAT(event_type, ':', TO_STRING(id))
        ORDER BY event_ts DESC, id DESC
        SEPARATOR ' / '
        LIMIT 3
    ) AS recent_events
FROM activity
WHERE MATCH('refund')
GROUP BY user_id
ORDER BY user_id ASC;
```

```text
+---------+----------------------------------------------+
| user_id | recent_events                                |
+---------+----------------------------------------------+
|     101 | retried:1007 / queued:1006 / checked:1005    |
|     202 | completed:2005 / queued:2004 / approved:2002 |
|     303 | rejected:3003 / requested:3001               |
+---------+----------------------------------------------+
```

In this syntax, `SEPARATOR` comes before `LIMIT`. Manticore doesn't escape anything or add quotes: the result is a regular string, not a JSON array.

This works well for `event_type` because those values are controlled by the application. Be more careful with arbitrary text: if the separator appears in the data itself, the result can no longer be parsed reliably. In that case, it's better to return separate rows or use a structured format.

## Where the new approach doesn't work

This form of `GROUP_CONCAT()` has several limitations. It works only in SQL queries with an explicit `GROUP BY` and does not support:

* `DISTINCT`, `OFFSET`, or combining multiple expressions at once;
* `JOIN`, `FACET`, outer `SELECT` queries, or table functions;
* KNN and hybrid queries, or scroll;
* implicit grouping or equivalent aggregation syntax in the JSON API.

Its alias cannot be used in `HAVING` or the final `ORDER BY`. The groups themselves can still be sorted by the grouping key and regular aggregates - for example, by `user_id` and `COUNT(*)`, as in the query above.

Memory usage is another consideration. For each such expression, Manticore stores a separate top-N for every group that remains in the result. The more groups there are, the higher `N` is, and the more expressions you use, the more memory is required. The size of the values and sort keys also affects memory usage, so it is best not to set an unnecessarily high limit "just in case".

Full documentation for the new functionality [is available here](https://manual.manticoresearch.com/Searching/Grouping#GROUP_CONCAT%28field%29).

## More examples

Support events are just one possible scenario. The new mode can be useful anywhere a document already contains a group key, the value you want to collect, and a field to sort by:

* **Product images:** collect the first few IDs or paths for each `product_id`, sorted by `display_order`.
* **Priority tasks:** return up to six task IDs for each `assignee_id`, sorted by a precomputed `priority`.
* **Server errors:** show the latest N errors for each server while keeping their total count separately.

If you need a short list of IDs, names, or paths, `GROUP_CONCAT(... ORDER BY ... LIMIT N)` now lets you get it in a single query. We hope you find this useful.