SQL - Remove duplicates to show the latest date record

Question

I have a view which ultimately I want to return 1 row per customer.

Currently its a Select as follows;

SELECT 
    Customerid,
    MAX(purchasedate) AS purchasedate,
    paymenttype,
    delivery,
    amount,
    discountrate 
FROM    
    Customer 
GROUP BY
    Customerid,
    paymenttype,
    delivery,
    amount,
    discountrate

I was hoping the MAX(purchasedate) would work but when I do my groupings it breaks as sometimes there could be a discountrate, sometimes its NULL, paymenttype can differ for each customer also, is there anyway just to show the last purchase a customer makes?

Solution

since SQL Server 2008 r2 supports windows function,

SELECT  Customerid,
        purchasedate,
        paymenttype,
        delivery,
        amount,
        discountrate
FROM    
    (
        SELECT  Customerid,
                purchasedate,
                paymenttype,
                delivery,
                amount,
                discountrate,
                ROW_NUMBER() OVER (Partition By CustomerID
                                    ORDER BY purchasedate DESC) rn
        FROM    Customer
    ) derivedTable
WHERE   derivedTable.rn = 1

or by using Common Table Expression

WITH derivedTable
AS
(
    SELECT  Customerid,
            purchasedate,
            paymenttype,
            delivery,
            amount,
            discountrate,
            ROW_NUMBER() OVER (Partition By CustomerID
                                ORDER BY purchasedate DESC) rn
    FROM    Customer
)
SELECT  Customerid,
        purchasedate,
        paymenttype,
        delivery,
        amount,
        discountrate
FROM    derivedTable
WHERE   derivedTable.rn = 1

or by using join with subquery which works in other DBMS

SELECT  a.*
FROM    Customer a
        INNER JOIN
        (
            SELECT  CustomerID, MAX(purchasedate) maxDate
            FROM    Customer
            GROUP BY CustomerID
        ) b ON  a.CustomerID = b.CustomerID AND
                a.purchasedate = b.maxDate

Comments

[DtotheG]

went with the bottom option as thats the one I understood best and worked a treat! Thank you

[Conrad Frix]

@user1505127 you should realize that the bottom option is not equivalent to the first two. You can get more than one record back from MAX() if there's a tie for PurchaseDate for a given CustomerID. I don't know if that's desirable for you or not. As an aside the windows function version of the of the MAX() query would use RANK()