I have the following Customer table:
The desired output is:
CustomerId = 1
OrderDate = 02-FEB-2014
CustomerEmail = email@example.com
I need to get the latest order date and related email from this table.
I tried to do the following:
Select CustomerId, MAX(OrderDate) OVER (PARTITION BY CustomerID), MAX(CustomerEmail) OVER (PARTITION BY CustomerId)
FROM Customer table;
Though this sql is returning the MAX(orderDate) correctly, the MAX(CustomerEmail) returned is not the one associated to the Max(orderdate) but the one with highest alphabetical order (i.e, in this case Zy2013@yahoo.com).
How do I get both the MAX(OrderDate) and its associated CustomerEmail in the same query using MAX as well as OVER PARTITION by approach?