LISTAGG in Oracle to return distinct values

Question

I am trying to use the LISTAGG function in Oracle. I would like to get only the distinct values for that column. Is there a way in which I can get only the distinct values without creating a function or a procedure?

  col1  col2 Created_by

   1     2     Smith 

   1     2     John 

   1     3     Ajay 

   1     4     Ram 

   1     5     Jack 

I need to select col1 and the LISTAGG of col2 (column 3 is not considered). When I do that, I get something like this as the result of LISTAGG: [2,2,3,4,5]

I need to remove the duplicate '2' here; I need only the distinct values of col2 against col1.

Answer 1

19c and later:

select listagg(distinct the_column, ',') within group (order by the_column)

from the_table

18c and earlier:

select listagg(the_column, ',') within group (order by the_column)

from (

   select distinct the_column 

   from the_table

) t

If you need more columns, something like this might be what you are looking for:

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;