Stumped on a complex GROUP BY

Question

Given the following query, how do I group by con_num and retrieve the lead_id with the MAX date_entered? I am using SQL Server 2012.

SELECT leads.id AS lead_id 
    , leads.date_entered 
    , so.con_num 
    , so.ord_date 
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

For example, the current query returns (in part) the following two rows...

lead_id	date_entered	con_num	ord_date
1	2021-04-20	N0226091	2021-04-22
2	2021-04-21	N0226091	2021-04-22

The query should return one row per con_num that contains the latest lead_id along with its entered_date...

lead_id	date_entered	con_num	ord_date
2	2021-04-21	N0226091	2021-04-22

Thank you.

Answer 1

Use ROW_NUMBER() window function to get each so.con_num's latest lead_id/date_entered row.

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