How to filter DISTINCT records and ordering them using LISTAGG function-CodePudding

 SELECT 
           s_id
          ,CASE WHEN LISTAGG(X.item_id, ',') WITHIN GROUP (ORDER BY TRY_TO_NUMBER(Z.item_pg_nbr))= '' THEN NULL
           ELSE LISTAGG (X.item_id, ',') WITHIN GROUP (ORDER BY TRY_TO_NUMBER(Z.item_pg_nbr)) 
           END AS item_id_txt
    FROM table_1 X
    JOIN table_2 Z
    ON Z.cmn_id = X.cmn_id
    WHERE s_id IN('38301','40228')
    GROUP BY s_id;

When I run the above query, I'm getting the same values repeated for ITEM_ID_TXT column. I want to display only the DISTINCT values.

S_ID                               ITEM_ID_TXT 

38301                            618444,618444,618444,618444,618444,618444,36184
40228                            616162,616162,616162,616162,616162,616162,616162

I also want the concatenated values to be ordered by item_pg_nbr

I can use DISTINCT in the LISTAGG function but that won't give the result ordered by item_pg_nbr.

Need your inputs on this.

CodePudding user response：

Since you cannot use different columns for the distinct and order by within group, one approach would be:

1 Deduplicate while grabbing the minimum item_pg_nbr.

2 listagg and order by the minimum item_pg_nbr.

create or replace table T1(S_ID int, ITEM_ID int, ITEM_PG_NBR int);

insert into T1 (S_ID, ITEM_ID, ITEM_PG_NBR) values
(1, 1, 3),
(1, 2, 9), -- Adding a non-distinct ITEM_ID within group
(1, 2, 2),
(1, 3, 1),
(2, 1, 1),
(2, 2, 2),
(2, 3, 3);

with X as
(
    select S_ID, ITEM_ID, min(ITEM_PG_NBR) MIN_PG_NBR 
    from T1 group by S_ID, ITEM_ID
)
select S_ID, listagg(ITEM_ID, ',') within group (order by MIN_PG_NBR)
from X group by S_ID
;

I guess the question then becomes what happens when you have duplicates within group? It would seem logical that the minimum item_pg_nbr should be used for the order by, but you could just as easily use the max or some other value.