从连接表中删除重复记录，并留下最后一次发生的记录

Question

我有两个表一个表A有一些记录。表B有一些修改的日期，插入表a的日期。现在我必须连接两个表，得到前3条记录，没有重复。

**Table A** 
id  name 
1   Arul
2   siva
3   raja
4   sanju
**Table B**
Id modifed TabId(Refered Table A) Modified Date
1  inserted    1                  2014-08-08 10:46:42.250
2  updated     1                  2014-08-08 10:46:42.624
3  inserted    2                  2014-08-08 10:46:42.724
4  inserted    3                  2014-08-08 10:46:43.624
5  inserted    4                  2014-08-08 10:46:44.624
6  updated     4                  2014-08-08 10:47:42.624
7  updated     4                  2014-08-08 10:48:42.624
8  updated     3                  2014-08-08 10:49:42.624   

Answer 1

SELECT  *
FROM    (SELECT a.Id, a.Name,b.ModifiedDate,
                ROW_NUMBER() OVER (PARTITION BY a.Id ORDER BY b.ModifiedDate) AS RowNumber
         from tableA a, tableB b where a.id  = b.TabId ) AS temp
WHERE   temp.RowNumber = 1     

Answer 2

试一下

 Select distinct top 3 a.*,Modified Date from tablea a 
inner join tableb b on (a.Id=b.TabId) 
order by Modified Datedesc;

Sql小提琴