the different partion by fields value but get the greater than 1 row number when using row_number

1 vote

0 answers

27 views

                          I want to keep the first row of dulplicate records in PostgreSQL 14, this is the sql look like:


    WITH xzqh_info AS ( 
        SELECT  
            xzqhdm,  
            xzqhmc 
        FROM  
            area_table 
        WHERE 1=1  
        and sfyx = '1' 
        AND xzqhdm IN ('411122')  
    ),  
    latest_records AS ( 
        SELECT  
            a.bdcdy_id, 
            b.bddjzmh_wz, 
            a.djlx,  
            a.qllx,
            a.djdy_id,
            ROW_NUMBER() OVER (PARTITION BY a.bdcdy_id, b.bddjzmh_wz ORDER BY a.dbsj DESC) AS rn 
        FROM base_table a 
        LEFT JOIN another_biz_table b ON a.djdy_id = b.djdy_id 
        WHERE a.sfbcx = '0' 
        AND a.qllx IN ('qllx036', 'qllx039') 
        AND a.dbsj BETWEEN to_date('2025-07-01', 'YYYY-MM-DD') AND (to_date('2025-07-01', 'YYYY-MM-DD') + interval '1 month' - interval '1 day') 
        AND a.xzqh IN ('411122') 
    )
    
    select * 
    from latest_records 
    where djlx = 'djlx001' 
    AND qllx = 'qllx036'



now I found the result with row number 2 records, but the a.bdcdy_id, b.bddjzmh_wz look like not the same, I have recheck again and again and still did not figure out where is going wrong. This is the result record:





Am I missing something?
                        

Asked by Dolphin (939 rep)

Jul 5, 2025, 02:36 PM

the different partion by fields value but get the greater than 1 row number when using row_number

Related Questions