How Can I Get Distinct Values Using Oracle's LISTAGG Function?

Oracle LISTAGG: Extracting Unique Values

The Oracle LISTAGG function, while powerful, can present challenges when aiming for unique values within a column. This article outlines efficient solutions without needing extra functions or procedures.

Oracle 19c and Above:

Oracle 19c and later versions offer a streamlined approach:

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

This directly aggregates distinct values, providing a concise and effective method.

Oracle 18c and Earlier:

For older Oracle versions, 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 creates a temporary table (t) containing only distinct values before LISTAGG is applied.

Handling Multiple Columns:

To include additional columns alongside the unique LISTAGG values, utilize this query:

<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 ensures each unique col2 value is correctly paired with its corresponding col1 value, providing a more complete result set.

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