asked on

MAX of two columns in one SQL

Hello,
I have the following Customer table:

The desired output is:
CustomerId = 1
OrderDate = 02-FEB-2014
CustomerEmail = angel@yahoo.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;

Open in new window

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?

Thanks!

Anwar Saiah

Select CustomerId, MAX(OrderDate),CustomerEmail OVER (PARTITION BY OrderDate), FROM Customer table;

ASKER CERTIFIED SOLUTION

Anoo S Pillai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

There are a couple of ways of doing that this is one:

SELECT  c.CustomerId, c.OrderDate, c.CustomerEmail
FROM    [Customer table] c
        INNER JOIN (SELECT  CustomerId,
                            MAX(OrderDate) LastOrderDate
                    FROM    [Customer table]
                    GROUP BY CustomerId
                   ) d ON c.CustomerId = d.CustomerId
                          AND c.OrderDate = d.LastOrderDate