I want to apply distinct on only CustomerID and get the latest record as I have RecordUpdate_date column in my table1.
I wrote this query but I am missing some rows(records) and getting duplicate records.
Please help me with that.
Thanks
Table1:
CustomerID, CustomerName, UpdateDate
Table2:
CustomerID, DateofBirth
My Query:
SELECT a.CustomerID
,a.CustomerName
,a.RecordUpDate_date
,b.DateofBirth
FROM Table1 AS a
INNER JOIN (
SELECT CustomerID
,MAX(RecordUpdate_date) AS max_RecordUpdate_date
FROM Table1
GROUP BY CustomerID
) AS abc
ON abc.CustomerID = a.CustomerID
AND abc.max_RecordUpdate_date = a.RecordUpdate_date
INNER JOIN Table2 AS b
ON b.CustomerID = a.CustomerID
INNER JOIN (
SELECT CustomerID
,MAX(DateofBirth) AS max_dob
FROM table2
GROUP BY CustomerID
) AS m
ON m.CustomerID = a.Customer
AND m.max_cus = c.DateofBirth
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…