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

Using LISTAGG function in Oracle to retrieve unique values

Oracle's LISTAGG function can join the values ​​in the specified columns. However, this function may produce duplicate results when retrieving values ​​from non-unique columns.

Question:

Use the LISTAGG function when you want to retrieve only unique values ​​from a column without having to use a function or procedure. For example, consider the following table:

col1	col2
1	2
1	2
1	3
1	4
1	5

When you apply LISTAGG to col2, you might get the following result: [2,2,3,4,5]. The goal is to eliminate duplicate 2's and only show unique values.

Solution:

19c and above:

<code class="language-sql">select listagg(distinct col2, ',') within group (order by col2)
from the_table;</code>

18c and earlier:

<code class="language-sql">select listagg(col2, ',') within group (order by col2)
from (
   select distinct col2 
   from the_table
) t;</code>

These queries use the DISTINCT keyword in the LISTAGG function to ensure that only unique values ​​are included.

Additional columns:

If you need to retrieve multiple columns along with unique values, you can do the following:

<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 query assigns a unique row number to each combination of col1 and col2 and then selects the first row for each col1 group.

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