Duplicate data cleaning query

1

I have a table with identified duplicate email addresses (table1). I want to keep just one unique and merge rest others. For that I have a logic where I want to select only those records who have 'last order date' as the latest as the surviving record and merge others. But if there is no last order date, then the criteria moves further and selects 'last login date' for that record as survivor.

How do I write a query in SQL server to get the both conditions fulfilled?

Please help. Thank you!

2

Select email_id
,case order_date
when NULL then login_date
else order_date
end as last_date
from(
Select email_id
,order_date
, login_date
,row_number() over(partition by email_id order by order_date desc) as rw
from records ) tbl where rw=1
)tbl