Priority   Attribute  sourceID
1          EMAIL      1
2          EMAIL      2
3          EMAIL      3
2          NAME       1
1          NAME       2
3          NAME       3

CustomerID  Name  Email              SourceID   Date
1           John  NULL               1          03/01/2017
1           NULL  John@email.com     3          01/01/2017
1           J     J.Smith@email.com  2          02/01/2017

CustomerID  Name  Email
1           John  J.Smith@email.com

select distinct
FIRST_VALUE(c.Name IGNORE NULLS)
OVER(PARTITION BY p.customerID
ORDER BY r.PRIORITY, c.DATE
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EMAIL,
FIRST_VALUE(c.Email IGNORE NULLS)
OVER(PARTITION BY c.customerID
ORDER BY r.PRIORITY, c.DATE
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS EMAIL,
from Customer c
join reference r on c.sourceID = r.sourceID
;