For a database with multiple rows per e-mail address, I want to group by each email address, taking the "most recent" information for each e-mail address.
Email Col1 Col2 Col3 Col4 CustomerID
======= ==== ==== ==== ==== ==========
a@a.com a a a null 1
a@a.com null b b null 2
a@a.com null null c null 3
I want to take the non-null value with the highest CustomerID. For above, I'd expect:
Email Col1 Col2 Col3 Col4
======= ==== ==== ==== ====
a@a.com a b c null
I can do a GROUP BY, taking the MAX for each column, but it's simply the highest value alphabetically, and doesn't take CustomerID into consideration.
SELECT EmailAddress, MAX(FirstName), MAX(LastName), MAX(Gender), MAX(Birthday), MAX(Country)
FROM CustomerInfo
GROUP BY EmailAddress
Additionally, this is being programmed in Exact Target, meaning some SQL keywords are unsupported, most notably variables, temp tables, and cursors are not supported.
Given these restrictions, is it possible to get the desired results?
If I'm understanding your question correctly, I think you'll need to join the table to itself multiple times. Something like this should work using a common table expression to get the max customer id per each column where the column isn't null. Then it joins back to itself to get the value:
with cte as (
select email,
max(case when col1 is not null then customerid end) maxcustomerid1,
max(case when col2 is not null then customerid end) maxcustomerid2,
max(case when col3 is not null then customerid end) maxcustomerid3,
max(case when col4 is not null then customerid end) maxcustomerid4
from yourtable
group by email
)
select t.email,
t1.col1,
t2.col2,
t3.col3,
t4.col4
from cte t
left join yourtable t1 on t.email = t1.email and t.maxcustomerid1 = t1.customerid
left join yourtable t2 on t.email = t2.email and t.maxcustomerid2 = t2.customerid
left join yourtable t3 on t.email = t3.email and t.maxcustomerid3 = t3.customerid
left join yourtable t4 on t.email = t4.email and t.maxcustomerid4 = t4.customerid