find only the duplicate values in listagg

I am having some duplicate rows in my table. I wanted to find those duplicates.

After i have done listagg on the column rp_num my table will have duplicate value like this :

 SELECT distinct vt.vsd_id, vt.rn,vt.rp_id,
 LISTAGG(vt.rp_num,' ') WITHIN GROUP (ORDER BY Nlssort(vt.rp_num, 'NLS_SORT=BINARY_CI')) 
                  over (PARTITION BY vt.vsd_id, vt.rn,vt.rp_id ) vp_num_gruppe 
             FROM   vechicle_datatype vt
            where vt.vsd_id = 37897;

I wanted to select only those duplicate values in rp_num_group (after the listagg on rp_num column). I tried using count(*) for the listagg but it doesnt work.

I wanted to select only those values mentioned below :

Answer

You can use the analytical function as follows:

SELECT * FROM 
(SELECT T.*, 
        COUNT(1) OVER (PARTITION BY VT.VSD_ID, VT.RN, VP_NUM_GRUPPE) AS CNT
   FROM (SELECT VT.VSD_ID, VT.RN, VT.RP_ID,
                LISTAGG(VT.RP_NUM,' ') WITHIN GROUP(
                   ORDER BY NLSSORT(VT.RP_NUM,'NLS_SORT=BINARY_CI')) AS VP_NUM_GRUPPE
           FROM VECHICLE_DATATYPE VT
          WHERE VT.VSD_ID = 37897
       GROUP BY VT.VSD_ID, VT.RN, VT.RP_ID)T )
 WHERE CNT > 1;