SQL - Remove duplicates to show the latest date record

sql, sql-server-2008-r2, t-sql

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

Problem

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?

Original source