title: Need help in using LISTAGG or REGEXP_REPLACE with DISTINCT function
body: <pre><code>SELECT  distinct ID,LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') 
  WITHIN GROUP(ORDER BY CAST(PAGE_NBR as INT)) AS No_Of_Views
  FROM db_name.schema_name.tbl
  WHERE date_created = '2022-01-21'
  AND flg_col = '0'
  AND src_id NOT IN ('0','3','4','5')
  AND ID='0127435345345'
  group by ID;
  </code></pre>
  <p>When I run the above query, I'm getting distinct values</p>
  <p>21/01/2022 20:21:17,21/01/2022 20:21:31,21/01/2022 20:22:23,21/01/2022 20:22:33</p>
  <p>When I try to run the same query using CTE, I'm not getting distinct results. Is there anyway to
  form a query using CTE that will fetch distinct values:</p>
  <pre><code>WITH CTE AS (
  SELECT
   ID,CREATE_TS,
  PAGE_NBR::INT AS PAGE_NBR
  FROM db_name.schema_name.tbl 
  WHERE flg_col = '0'
  AND src_id NOT IN ('0','3','4','5')
  AND CREATE_DT = ''2022-21-01''
  AND ID='0127435345345'
  ORDER BY PAGE_NBR
  )
  SELECT
   ID,
  LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',') WITHIN GROUP(ORDER BY CREATE_TS) AS No_Of_Views 
  FROM CTE
  GROUP BY ID;
  </code></pre>
  <p>When I use a distinct inside LISTAGG ie. LISTAGG(to_varchar(CREATE_TS, 'mm/dd/yyyy hh:mi:ss'), ',')
  I'm getting SQL compilation error: [CTE.CREATE_TS] is not a valid order by expression</p>
  <p>I want the result same as the first query but I need to try the second method..</p>