SQL select with unique_list – losing an item

oracle

I have an oracle database and I have the following problem:
If I make an select like this:

SELECT fk_proddes_id,fk_id_cod_deseu,
     LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare)
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

I get the following result:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 11,D 11,D 7
301             506             D 1,D 15

I need the unique code, so if I put:

SELECT fk_proddes_id,fk_id_cod_deseu,
    unique_list ( LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare))
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

the result is:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 11,D 7
301             506             D 15

so, I "lose" the D1 – don't know why…

If I put

SELECT fk_proddes_id,fk_id_cod_deseu,
    unique_list ( LISTAGG (TO_CHAR (cod_eliminare), ',')
                    WITHIN GROUP (ORDER BY cod_eliminare DESC))
     AS cod_eliminare
FROM sim_sd.proddes_ag_elim
GROUP BY fk_proddes_id, fk_id_cod_deseu

the result is:

FK_PRODDES_ID   FK_ID_COD_DESEU COD_ELIMINARE
53              112             D 7,D 11
301             506             D 15,D 1

But I need D1,D15 and D11,D7. What's wrong with the second query? Why do I "lose" D1 ???

Thanks!

Best Answer

There are two ways: first - using regexp_replace like here - Eliminate duplicates in ListAgg (Oracle), second - use distinct inside a subquery:

select fk_proddes_id,fk_id_cod_deseu, 
       listagg(to_char(cod_eliminare), ',') within group (order by cod_eliminare desc)
  from (select distinct fk_proddes_id,fk_id_cod_deseu, cod_eliminare
          from sim_sd.proddes_ag_elim)
 group by fk_proddes_id, fk_id_cod_deseu