这应该对你有帮助

create table #sample (type char(1), date datetime, Id bigint)
insert into #sample values('A', '5/22/2019 4:33', 1065621)
insert into #sample values('A', '5/22/2019 4:33', 1065181)
insert into #sample values('A', '5/22/2019 4:33', 1064212)
insert into #sample values('B', '11/7/2017 1:07', 540180)
insert into #sample values('B', '11/7/2017 1:07', 540179)
insert into #sample values('B', '11/7/2017 1:07', 540177)

select * from #sample

select [type], [date], max(id)
from #sample
group by [type], [date]

select distinct [type], [date], max(id) over(partition by  [type], [date] )
from #sample

Drop table #sample

可以使用子查询进行筛选。假设表的列被调用 id , date 以及 col ，即：

select t.*
from mytable t
where t.col = (select max(t1.col) from mytable t1 where t1.id = t.id)

对于性能，请考虑 (id, col) .

一种有效的方法（使用正确的索引）是相关子查询：

select t.*
from t
where t.individual = (select max(t2.individual) from t t2 where t2.id = t.id);

右索引已打开 (id, individual) .