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

[githubmanticore]

Originally published on the Manticore Search website on September 4, 2026

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, values inside GROUP_CONCAT() can be sorted and limited to the number you need:

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.

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:

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.

+---------+----------------+-------------------------------+
| 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:

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.

+------+---------+------------+
| 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.

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;

+---------+----------------+--------------------------+
| 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:

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;

+---------+----------------------------------------------+
| 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.

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.