returning partially distinct/unique rows

Question

Hi all,

I need to create a query which groups by two columns and returns an additional column based on a condition.

For example, say I've got the following columns:

ProductCode | SerialNumber | Quantity | DatePurchased | CustomerID

and the table contains duplicate combinations of ProductCode and SerialNumber with differing Quanitites and Purchase Dates. I'd like to return the ProductCode, SerialNumber and the Quantity for the row with greatest (most recent) value for DatePurchased. To further complicate things this must be done for all rows where CustomerID = 'xxx'.

Any ideas???

Any help appreciated. Cheers.

Answer 1

Assuming unique DatePurchased:

SELECT a.*
FROM Table as a
JOIN (
   SELECT ProductCode, SerialNumber, MAX(DatePurchased) as MaxDate 
   FROM Table
   GROUP BY ProductCode, SerialNumber
) as b ON
   a.ProductCode = b.ProductCode
   AND a.SerialNumber = b.SerialNumber
   AND a.DatePurchased = b.MaxDate
WHERE
   a.CustomerID = 'xxx'

Answer 2

SELECT
     T1.ProductCode,
     T1.SerialNumber,
     T1.Quantity
FROM
     Some_Table T1
LEFT OUTER JOIN Some_Table T2 ON
     T2.ProductCode = T1.ProductCode AND
     T2.SerialNumber = T1.SerialNumber AND
     T2.CustomerID = T1.CustomerID AND
     T2.DatePurchased > T1.DatePurchased
WHERE
     T1.CustomerID = 'xxx' AND
     T2.ProductCode IS NULL