SQL question about GROUP BY

I've been using SQL for a few years, and this type of problem comes up here and there, and I haven't found an answer. But perhaps I've been looking in the wrong places - I'm not really sure what to call it.

For the sake of brevity, let's say I have a table with 3 columns: Customer, Order_Amount, Order_Date. Each customer may have multiple orders, with one row for each order with the amount and date.

My Question: Is there a simple way in SQL to get the DATE of the maximum order per customer?

I can get the amount of the maximum order for each customer (and which customer made it) by doing something like:

SELECT Customer, MAX(Order_Amount) FROM orders GROUP BY Customer;

But I also want to get the date of the max order, which I haven't figured out a way to easily get. I would have thought that this would be a common type of question for a database, and would therefore be easy to do in SQL, but I haven't found an easy way to do it yet. Once I add Order_Date to the list of columns to select, I need to add it to t开发者_JAVA百科he Group By clause, which I don't think will give me what I want.

Apart from self-join you can do:

SELECT o1.*
FROM orders o1 JOIN orders o2 ON o1.Customer = o2.Customer
GROUP BY o1.Customer, o1.Order_Amount
HAVING o1.Order_Amount = MAX(o2.Order_Amount);

There's a good article reviewing various approaches.

And in Oracle, db2, Sybase, SQL Server 2005+ you would use RANK() OVER.

SELECT * FROM (
    SELECT *
    RANK() OVER (PARTITION BY Customer ORDER BY Order_Amount DESC) r
    FROM orders) o 
WHERE r = 1;

Note: If Customer has more than one order with maximum Order_Amount (i.e. ties), using RANK() function would get you all such orders; to get only first one, replace RANK() with ROW_NUMBER().

There's no short-cut... the easiest way is probably to join to a sub-query:

SELECT
    *
FROM
    orders JOIN
    (
        SELECT Customer, MAX(Order_Amount) AS Max_Order_Amount
        FROM orders 
        GROUP BY Customer
    ) maxOrder 
        ON maxOrder.Customer = orders.Customer 
        AND maxOrder.Max_Order_Amount = orders.Order_Amount

you will want to join on the same table...

SELECT Customer, order_date, amt 
FROM orders o,
    ( SELECT Customer, MAX(Order_Amount) amt FROM orders GROUP BY Customer ) o2
WHERE o.customer = o2.customer
AND o.order_amount = o2.amt
;

Another approach for the collection:

WITH tempquery AS 
(
    SELECT 
         Customer
        ,Order_Amount
        ,Order_Date
        ,row_number() OVER (PARTITION BY Customer ORDER BY Order_Amount DESC) AS rn
    FROM 
        orders 
)
SELECT 
     Customer
    ,Order_Amount
    ,Order_Date
FROM
    tempquery
WHERE
    rn = 1

If your DB Supports CROSS APPLY you can do this as well, but it doesn't handle ties correctly

SELECT [....]
FROM Customer c
CROSS APPLY 
(SELECT TOP 1 [...] 
 FROM Orders o
 WHERE c.customerID = o.CustomerID
 ORDER BY o.Order_Amount DESC) o

See this data.SE query

You could try something like this:

SELECT Customer, MAX(Order_Amount), Order_Date 
FROM orders  O
WHERE ORDER_AMOUNT = (SELECT MAX(ORDER_AMOUNT) FROM orders WHERE CUSTOMER = O.CUSTOMER)
GROUP BY CUSTOMER, Order_Date 

 with t as
(
 select  CUSTOMER,Order_Date ,Order_Amount,max(Order_Amount) over (partition 
 by Customer) as
 max_amount from orders
)
select * from t  where t.Order_Amount=max_amount