0

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!

2
  • What is unique_list in your code? Do you have to use it or you need any way to make distinct values?
    – Dmitriy
    Mar 27, 2015 at 9:33
  • just distinct values...
    – WDrgn
    Mar 27, 2015 at 12:46

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.