Need guidance in using ORDER BY when using LISTAGG

March 15, 2022

SELECT B.ID,
LTRIM(LISTAGG(ITEM_ID,';')WITHIN GROUP(ORDER BY B.PG_NO), '0') as PROCESS_ID
FROM table1 A
JOIN table2 B
ON A.CAS_ID=B.CAS_ID
WHERE B.DATE = '2022-03-03'
AND B.ID IS NOT NULL
AND P_CD NOT IN ('1','2','5','7')
AND A.ID IN(12690222,24515955)
GROUP BY B.ID

When I run the above query, I’m getting results as mentioned below:

ID           PROCESS_ID

12690222     5544973696;5544973696;5544973696;5544973696
24515955     777239598;777239598;777239598

I want to display only the distinct values in PROCESS_ID column and the results should be ordered by PG_NO column. Kindly guide me how to achieve this.

>Solution :

You should be able to use DISTINCT with LISTAGG:

SELECT B.ID,
       LTRIM(LISTAGG(DISTINCT ITEM_ID, ';') WITHIN GROUP
           (ORDER BY B.PG_NO), '0') AS PROCESS_ID
FROM table1 A
INNER JOIN table2 B ON A.CAS_ID = B.CAS_ID
WHERE B.DATE = '2022-03-03' AND
      B.ID IS NOT NULL AND
      P_CD NOT IN ('1', '2', '5', '7') AND
      A.ID IN (12690222, 24515955)
GROUP BY B.ID;