Stumped on a complex GROUP BY[duplicate]

使用 ROW_NUMBER() window函数来获取每个so.con_num的最新lead_id/date_entered行。

select lead_id, date_entered, con_num, ord_date
from
(
    SELECT leads.id AS lead_id 
        , leads.date_entered 
        , so.con_num 
        , so.ord_date
        , row_number() over (partition by so.con_num
                             order by leads.id desc, leads.date_entered desc) rn
    FROM crm.leads 
        INNER JOIN crm.contacts ON leads.contact_id = contacts.id 
        INNER JOIN crm.email_addr_bean_rel rel ON rel.bean_id = contacts.id 
        INNER JOIN crm.email_addresses email ON email.id = rel.email_address_id 
        INNER JOIN sales_order so ON so.bill_email = email.email_address OR so.dt_email = email.email_address 
    WHERE rel.bean_module = 'contacts' AND so.ord_date >= leads.date_entered
) dt
where rn = 1