方式获得最新的记录。我一直使用的个人资料是使用 ROW_NUMBER（） http://msdn.microsoft.com/en-us/library/ms186734.aspx 。

首先在CTE中，我们基于customerid和desc date字段对行进行编号。接下来，您选择 rn = 1 这只会为每个客户获取最新记录。

 ; 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 
其中a.rn = 1 
 

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

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