How do you de-duplicate values inside STRING_AGG, and why can DISTINCT plus ORDER BY fail?

## Why duplicates show up at all A concatenated list repeats whatever the group contains. Duplicates arrive from two directions: the source table genuinely holds repeated values (a tag applied twice, an event type recorded many times), or the query's joins multiplied rows before grouping. The remedy differs — the second is a join-shape problem that de-duplicating the aggregate only papers over — but the syntax question is the same: how do you emit each distinct value once? ## DISTINCT inside the aggregate SQL allows `DISTINCT` as a *set quantifier* on an aggregate's argument, and several engines extend that to the concatenation functions: ```sql -- PostgreSQL SELECT post_id, string_agg(DISTINCT tag, ', ' ORDER BY tag) AS tags FROM post_tags GROUP BY post_id; -- MySQL SELECT post_id, GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ', ') AS tags FROM post_tags GROUP BY post_id; ``` Support is not universal: SQL Server's `STRING_AGG` accepts no `DISTINCT`, and Oracle gained `DISTINCT` for `LISTAGG` only in a recent release. Before reaching for it, know whether your target engine has it — this is one of the spots where a query that works in development fails outright after a port, rather than merely behaving differently. ## Why DISTINCT and ORDER BY conflict When both appear, the aggregate does two things in sequence: it reduces the group's values to their distinct set, then it orders that set. Ordering can therefore only use information that survives de-duplication — that is, the aggregated expression itself. PostgreSQL states this directly, rejecting an aggregate whose `ORDER BY` expression does not appear in its argument list when `DISTINCT` is used: ```sql -- rejected: created_at is not in the argument list string_agg(DISTINCT tag, ', ' ORDER BY created_at) -- accepted string_agg(DISTINCT tag, ', ' ORDER BY tag) ``` The restriction is not pedantry. Suppose the tag `sql` appears on three rows with three different `created_at` values. After de-duplication there is one `sql` element — so which of the three timestamps should decide its position? The question has no answer, and rather than pick one silently, the engine refuses the query. Whenever you find yourself wanting "distinct values, ordered by something else", you are really asking for an aggregation over a *chosen representative* per value, which is a different query. ## The portable rewrite The rewrite that runs on every engine and dissolves the ordering restriction is to de-duplicate one level down and aggregate the already-unique rows: ```sql SELECT post_id, STRING_AGG(tag, ', ') WITHIN GROUP (ORDER BY tag) AS tags FROM (SELECT DISTINCT post_id, tag FROM post_tags) d GROUP BY post_id; ``` Note what the inner `SELECT DISTINCT` covers: the **group key plus the value**. De-duplicating on the value alone would collapse across posts and lose rows; including the grouping column keeps each post's tag set independent. Written as a CTE this reads even better and gives the intermediate set a name. This form is also where "distinct, ordered by something else" becomes expressible. Pick a representative in the inner query — say the earliest use of each tag — and the outer ordering has a well-defined key: ```sql SELECT post_id, STRING_AGG(tag, ', ') WITHIN GROUP (ORDER BY first_used) AS tags FROM (SELECT post_id, tag, MIN(created_at) AS first_used FROM post_tags GROUP BY post_id, tag) d GROUP BY post_id; ``` The inner aggregation collapses each (post, tag) pair to one row carrying its earliest timestamp; the outer aggregation then concatenates in that order. Two grouping levels, each with a clear job. ## Cost and correctness notes `DISTINCT` inside an aggregate is not free: the engine must materialise or sort each group's values to find the distinct set, so it does real work per group. That rarely matters for small groups and can matter for large ones — but it is the same work the derived-table rewrite does, just expressed differently, so the choice between them should be driven by portability and readability rather than by a performance hunch. The more important caution is diagnostic. If duplicates appeared because a join multiplied rows, `DISTINCT` inside the aggregate hides the symptom in *this* column while every other aggregate in the same `SELECT` — sums, counts, averages — is still computed over the inflated rows and remains wrong. Before adding it, confirm the duplicates are real data rather than an artifact of the query's shape.