How Can I Remove Duplicates When Using Oracle's LISTAGG Function?

Oracle LISTAGG: Handling Duplicate Values for Unique Results

Oracle's LISTAGG function, while powerful for aggregating data, doesn't inherently remove duplicate entries. It concatenates all values, including repetitions, into a single string. Here's how to achieve distinct value aggregation:

Method for Oracle 19c and Later:

Oracle 19c and later versions offer a simplified solution:

<code class="language-sql">SELECT LISTAGG(DISTINCT the_column, ',') WITHIN GROUP (ORDER BY the_column)
FROM the_table;</code>

This directly incorporates the DISTINCT keyword within the LISTAGG function itself.

Method for Oracle 18c and Earlier:

For older versions (18c and below), a subquery is necessary:

<code class="language-sql">SELECT LISTAGG(the_column, ',') WITHIN GROUP (ORDER BY the_column)
FROM (
  SELECT DISTINCT the_column
  FROM the_table
) t;</code>

This first selects only the unique values in a subquery, then applies LISTAGG to that result.

Including Multiple Columns:

To include additional columns and maintain uniqueness, a more advanced approach is required:

<code class="language-sql">SELECT col1, LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2)
FROM (
  SELECT col1, 
         col2,
         ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1) AS rn
  FROM foo
  ORDER BY col1, col2
)
WHERE rn = 1
GROUP BY col1;</code>

This uses ROW_NUMBER() to assign a unique rank to each combination of col1 and col2. The outer query then selects only the first occurrence (rn = 1) for each unique combination, ensuring that duplicates are eliminated before aggregation.

The above is the detailed content of How Can I Remove Duplicates When Using Oracle's LISTAGG Function?. For more information, please follow other related articles on the PHP Chinese website!