Data is not the same when Duplicate Visual

Hi All,

Data is not the same when Duplicate Visual.
How to fix it ?


this is my query.

with dib_salesforce as (
select * 

-- join table 
    row_number() over(partition by order by af.lastmodifieddate desc) idx 
    from "scbpt_dl_persistent"."sfdc_crm_application_form"  as af

    left JOIN "scbpt_dl_persistent"."sfdc_crm_ap_payment" as ap 
    on = ap.Application_Form__c

    left join "scbpt_dl_persistent"."sfdc_crm_lead" as l
    on  af.Lead__c  = l.Id
where (l.Status = 'Close Won - Issued Policy' or l.Status = 'Inspection' or l.Status = 'QC' or l.Status = 'Submitted App' or l.Status = 'Waiting for Payment') 
and (ap.isParent__c = true) and (l.Campaign_Code_F__c = 'HOTLEAD' or l.Campaign_Code_F__c = 'Digital Insurance') 
order by af_submit_date desc

select  * FROM dib_salesforce 
where (idx = 1) and (af_submit_date >= CAST('2023-01-01' AS DATE)) and (Payment_Status != 'Canceled' and Payment_Status != 'Failed')

Thanks for help.

Hi @Siriphon - Why you are using row_number? what is the exact requirement? I believe row_number is the issue.

Regards - Sanjeeb

i need to query last modify record in database.

Hi @Siriphon - I believe you should use rank or dense_rank function for the same not the row_number.

Regards - Sanjeeb