Distinct one column in two tables MS SQL Server 2008

Question

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

Answer 1

There are many ways to get only latest record. My personal that I use all the time is to use ROW_NUMBER() http://msdn.microsoft.com/en-us/library/ms186734.aspx.

First in CTE we number rows based on the customerid and desc date field. Next you select where rn =1 this gets only latest record for each customer.

;WITH   CTE
          AS (
               SELECT customerID
                   ,customerName
                   ,UpdateDate
                   ,ROW_NUMBER() OVER ( PARTITION BY customerID ORDER BY UpdateDate DESC ) AS rn
                FROM table1 AS a
             )
    SELECT a.customerID
           ,a.CustomerName
           ,a.UpdateDate
           ,b.DateOfBrith
        FROM CTE a
        JOIN table2 AS b
            ON a.customerId = b.CustomerID
        where a.rn = 1