title: Count with row_number function SQL CTE
body: <p>I have the below CTEs that work perfectly, but I want to count the "cl.memb_dim_id" by "cl.post_date" but I am not sure how to do that? When adding in the count function I get an error that highlights the ' row number' so I am assuming I cant have both order and group together ????</p>
  
  <pre><code>WITH 
  
  DATES AS 
  (
  select to_date('01-jan-2017') as startdate,to_date('02-jan-2017') as enddate
  
  
  from dual
  
  ),
  
  Claims as (select distinct 
  cl.memb_dim_id,
  row_number () over (partition by cl.Claim_number order by cl.post_date desc) as uniquerow,
  cl.Claim_number,
  cl.post_date,
  ct.claim_type,
  ap.claim_status_desc, 
  dc.company_desc,
  dff.io_flag_desc,
  pr.product_desc,
  cl.prov_dim_id,
  cl.prov_type_dim_id
  
  
  from dw.fact_claim cl
  
  inner join dates d
   on 1=1
  
  and cl.post_date &gt;= d.startdate
  and cl.post_date &lt;= d.enddate 
  and cl.provider_par_dim_id in ('2')
  and cl.processing_status_dim_id = '1'
  and cl.company_dim_id in ('581','585','586','589','590','591','588','592','594','601','602','603','606','596','598','597','579','599','578','577','573','574','576','575')
  
  
  left join dw.DIM_CLAIM_STATUS ap
  on cl.claim_status_dim_id = ap.claim_status_dim_id
  
  left join dw.dim_claim_type ct 
  on cl.claim_type_dim_id = ct.claim_type_dim_id 
  and cl.claim_type_dim_id in ('1','2','6','7')
  
  left join dw.DIM_COMPANY dc
  on cl.company_dim_id = dc.company_dim_id
  
  left join dw.DIM_IO_FLAG dff
  on cl.io_flag_dim_id = dff.io_flag_dim_id
  
  left join dw.dim_product pr
  on cl.product_dim_id = pr.product_dim_id
  
  )
  
  
  Select * from claims where uniquerow ='1'
  </code></pre>