Returning partially distinct / unique strings
I need to create a query that groups by two columns and returns an additional column based on a condition.
For example, let's say I have the following columns:
ProductCode | Serial number | Quantity | Purchase date | CustomerID
and the table contains repeated combinations of ProductCode and SerialNumber with different Quanitites and Dare. I would like to return ProductCode, SerialNumber and count for the row with the highest (last) value for DatePurchased. To complicate matters even more, this must be done for all rows where CustomerID = 'xxx'.
Any ideas ???
Any help is appreciated. Greetings.
+1
a source to share
2 answers
Assuming a 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'
+1
a source to share
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
0
a source to share